Here is the requirement:
Find all teachers whose FirstName length is less than 5 and the first 3 characters of their FirstName and LastName are the same
I tried this query (Scalar Function):
CREATE OR ALTER FUNCTION dbo.fn_TeacherFirstName (@TeacherID int) RETURNS NVARCHAR(20) AS BEGIN DECLARE @Result NVARCHAR(20) SELECT @Result = LEN(FirstName) < 5 AND LEFT(FirstName,3) = LEFT(LastName,3) FROM dbo.Teacher WHERE @TeacherID = ID RETURN @Result END
To call function:
--CALL FUNCTION select *, dbo.fn_TeacherFirstName (ID) AS Result from dbo.Teacher t
But, when I execute first query, it shows error:
Incorrect syntax near ‘<‘.
Can anyone help me with this?
Just use a normal query, not a function. Also there is no
boolean type in SQL Server, use
SELECT *, Result = CAST(CASE WHEN LEN(t.FirstName) < 5 AND LEFT(t.FirstName, 3) = LEFT(t.LastName, 3) THEN 1 END AS bit) FROM dbo.Teacher t