SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need help with creating function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Niki
Yak Posting Veteran

50 Posts

Posted - 12/20/2012 :  17:03:20  Show Profile  Reply with Quote
I have following for validating a student number. If it is within my filering conditions, I would like to return the student number. I don't know how to execute the SELECT statement to get the number. Please help!

Use dbTEST;
GO
IF OBJECT_ID (N'IsStudentNoValid', N'FN') IS NOT NULL
DROP FUNCTION IsStudentNoValid;
GO

Create FUNCTION IsStudentNoValid (@StudentNo Varchar(10) )
RETURNS Varchar(10)
AS
BEGIN
DECLARE @sisDB varchar(200),@sql varchar(max);
SET @sisDB = @@ServerName
IF @sisDB = 'PRODUCTION' SET @SisDB = @SisDB + '.PRD.'
IF @sisDB = 'TEST' SET @SisDB = @SisDB + '.TST.'

SET @sql = N'
Select Studentno from ' + @SisDB + 'dbo.tblSTU as Stu
where
datediff(yyyy, Stu.BirthDate, ''2012-08-20'') < 23 and Stu.sts <> ''D''
and Stu.StudentNo = @StudentNo'
-- would like to return the student number
RETURN(exec(@sql))
END;
GO



Niki

Edited by - Niki on 12/20/2012 17:04:50

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/20/2012 :  17:08:44  Show Profile  Reply with Quote
You will need to use sp_executesql rather than EXEC to return a value from a dynamic query. See the second example on this page

HOWEVER, you cannot use dynamic SQL in a UDF. So even if you did that, it would not work. Are you trying to write a function that is portable across servers and databases?
Go to Top of Page

Niki
Yak Posting Veteran

50 Posts

Posted - 12/20/2012 :  17:29:44  Show Profile  Reply with Quote
Thanks Sunita. I see what you are saying. Yes, I am trying to write portable function across servers/databases.
RETURN(EXECUTE sp_executesql (@sql)) wouldn't work either as it returns 0 or 1 but I am trying to get the student number back.

Niki
Go to Top of Page

Niki
Yak Posting Veteran

50 Posts

Posted - 12/20/2012 :  18:09:05  Show Profile  Reply with Quote
This is how I went around to do it -

BEGIN
DECLARE @sisDB varchar(200)
declare @RetStudentNo varchar(10)

if @@ServerName = 'TEST'
begin
Select @RetStudentNo = Studentno from
testserver.tesdb.dbo.tblstu as Stu where
datediff(yyyy, Stu.BirthDate, ''2012-08-20'') < 23 and Stu.sts <> ''D''
and Stu.StudentNo = @StudentNo'
end
else
begin
Select @RetStudentNo = Studentno from ProductionServer.Productiondb.dbo.tblstu as Stu where
datediff(yyyy, Stu.BirthDate, ''2012-08-20'') < 23 and Stu.sts <> ''D''
and Stu.StudentNo = @StudentNo'
end
return @RetStudentNo
END;

Niki
Go to Top of Page

robvolk
Most Valuable Yak

USA
15639 Posts

Posted - 12/20/2012 :  18:11:44  Show Profile  Visit robvolk's Homepage  Reply with Quote
Ummm, with the WHERE clause you're using, you'll be returning the same value that you're passing to the function as a parameter:
Create FUNCTION IsStudentNoValid (@StudentNo Varchar(10) )
...
Select Studentno from ' + @SisDB + 'dbo.tblSTU as Stu
where
datediff(yyyy, Stu.BirthDate, ''2012-08-20'') < 23 and Stu.sts <> ''D'' 
and Stu.StudentNo = @StudentNo'
Why not return a 1 if the student exists and 0 if they don't?

Regarding the database references, you should consider using a synonym for the student table, rather than referencing the table by name. That way each server can use the same synonym but it would refer to their appropriate tables:
-- on PRODUCTION server:
CREATE SYNONYM Students FOR PRD.dbo.tblSTU
GO
-- on TEST server:
CREATE SYNONYM Students FOR TST.dbo.tblSTU
GO
Your function then becomes:
CREATE FUNCTION IsStudentNoValid (@StudentNo VARCHAR(10) )
RETURNS VARCHAR(10)
AS
BEGIN
	DECLARE @student VARCHAR(10)
	SELECT @student= Studentno FROM Students
	WHERE BirthDate>DATEADD(yyyy, -23, '2012-08-20') AND sts <> 'D' 
	AND StudentNo = @StudentNo
	RETURN @student
END;
And IMHO really should be:
CREATE FUNCTION IsStudentNoValid (@StudentNo VARCHAR(10) )
RETURNS BIT
AS
BEGIN
	DECLARE @exists BIT=0;
	IF EXISTS(SELECT * FROM Students
	WHERE BirthDate>DATEADD(yyyy, -23, '2012-08-20') AND sts <> 'D' 
	AND StudentNo = @StudentNo)
		SET @exists=1
	RETURN @exists
END;
I modified the date arithmetic to better utilize any existing indexes on the BirthDate column. It's also more precise if you're looking for students who are 23 years old or older, using DATEDIFF() in the original could have included some 22 year olds. And if it will always be 23 years prior to August 20, 2012, might as well hardcode it to August 20, 1989.
Go to Top of Page

Niki
Yak Posting Veteran

50 Posts

Posted - 12/21/2012 :  12:39:53  Show Profile  Reply with Quote
Thanks, robvolk! I'm selecting 22 yrs or less. I'm not sure if SYNONYM will work, it still creates the SYNONYM at differnt servers and in different databases.

Niki
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000