Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need help with creating function

Author  Topic 

Niki
Yak Posting Veteran

51 Posts

Posted - 2012-12-20 : 17:03:20
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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-20 : 17:08:44
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

51 Posts

Posted - 2012-12-20 : 17:29:44
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

51 Posts

Posted - 2012-12-20 : 18:09:05
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

15732 Posts

Posted - 2012-12-20 : 18:11:44
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

51 Posts

Posted - 2012-12-21 : 12:39:53
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
   

- Advertisement -