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.
Need help with creating function
Posted - 12/20/2012 : 17:03:20
