writing a scalar-function

[mrkeivan]

Hey, I have the following code but I dunno why I get and error!!??

CREATE FUNCTION [dbo].[GetSubTeacher] (@ID Int)

RETURNS VARCHAR(100)

AS

BEGIN

DECLARE @TeacherName NVARCHAR(100)
IF (@ID = 0)

   SET @TeacherName = 'NO SUB'

IF (@ID <> 0)
   SET @TeacherName = SELECT FName + ' ' + LName AS \[Name\] from Teachers WHERE ID = @ID

RETURN @TeacherName

END

Incorrect syntax near the keyword 'SELECT'. Regards, K

[Goutam Patra]

Try this

CREATE FUNCTION [dbo].[GetSubTeacher] (@ID Int)

RETURNS VARCHAR(100)

AS

BEGIN

DECLARE @TeacherName NVARCHAR(100)
IF (@ID = 0) BEGIN

   SET @TeacherName = 'NO SUB'
END
IF (@ID <> 0) BEGIN
   SET @TeacherName = (SELECT FName + ' ' + LName AS \[Name\] from Teachers WHERE ID = @ID)
END

RETURN @TeacherName

END

modified on Monday, August 9, 2010 8:10 AM

[J4amieC]

You were very close:

IF (@ID <> 0)
SELECT @TeacherName = FName + ' ' + LName AS [Name] from Teachers WHERE ID = @ID

[PIEBALDconsult]

What's with the ifs? :wtf:

alter FUNCTION GetSubTeacher
(
@ID integer
)
RETURNS Varchar(100)
AS
BEGIN
RETURN ( SELECT CASE @ID WHEN 0 THEN 'NO SUB' ELSE ( SELECT FName+' '+LName FROM Teachers WHERE ID=@ID ) END )
END

[Mycroft Holmes]

Add in ISNULL() as well (you may need to move the WHEN) ;P

alter FUNCTION GetSubTeacher
(
@ID integer
)
RETURNS Varchar(100)
AS
BEGIN
RETURN ( SELECT CASE ISNULL(@ID,0) WHEN 0 THEN 'NO SUB' ELSE ( SELECT FName+' '+LName FROM Teachers WHERE ID=@ID ) END )
END

Never underestimate the power of human stupidity RAH

[PIEBALDconsult]

Well I don't like the "magic number" anyway; I'd rather simply search and return "NO SUB" if the ID doesn't exist. Besides, where'd they get the ID in the first place? :confused:

[Mycroft Holmes]

I'd rather use a left join instead of a function - this causes a sub select on every line, lousy design!

Never underestimate the power of human stupidity RAH

[PIEBALDconsult]

True, but we don't know how it'll be (mis-)used.