Skip to content

Incorrect syntax near ‘<' in SQL Server Scalar Functions

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?

Answer

Just use a normal query, not a function. Also there is no boolean type in SQL Server, use bit instead.

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