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 Stuwheredatediff(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.tblSTUGO-- on TEST server:CREATE SYNONYM Students FOR TST.dbo.tblSTUGO
Your function then becomes:CREATE FUNCTION IsStudentNoValid (@StudentNo VARCHAR(10) )RETURNS VARCHAR(10)ASBEGIN DECLARE @student VARCHAR(10) SELECT @student= Studentno FROM Students WHERE BirthDate>DATEADD(yyyy, -23, '2012-08-20') AND sts <> 'D' AND StudentNo = @StudentNo RETURN @studentEND;
And IMHO really should be:CREATE FUNCTION IsStudentNoValid (@StudentNo VARCHAR(10) )RETURNS BITASBEGIN 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 @existsEND;
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.