CASE statement in UDF 1

[Mantle51]

Hello,
I'm trying to create a System UDF that I can plug into any number of Select statements from various databases however, I keep getting error message 'Incorrect syntax near keyword CASE'

Thank you for your help

CREATE FUNCTION fn_tblEvalSwitch (@tblEvalField CHAR(1) )
RETURNS VARCHAR(20)
WITH SCHEMABINDING
AS
BEGIN
CASE @tblEvalField
WHEN 'M' THEN 'Month of'
WHEN 'D' THEN 'Day of'
WHEN 'Q' THEN 'Quarter ending'
WHEN 'Y' THEN 'Year ending'
END

 

[bborissov]

CASE can not be used outside of SELECT. You must use multiple IFs or one select:

CREATE FUNCTION fn_tblEvalSwitch (@tblEvalField CHAR(1) )
RETURNS VARCHAR(20)
WITH SCHEMABINDING
AS  
BEGIN
DECLARE @RetVal varchar(20)
SELECT @RetVal = CASE @tblEvalField
                      WHEN 'M' THEN 'Month of'
                      WHEN 'D' THEN 'Day of'
                      WHEN 'Q' THEN 'Quarter ending'
                      WHEN 'Y' THEN 'Year ending'
                 END
RETURN @RetVal
END

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ESquared]

CASE can not be used outside of SELECT

Not so...

CREATE FUNCTION fn_tblEvalSwitch (
   @tblEvalField char(1)
) RETURNS varchar(20)
AS  
BEGIN 
   [red]RETURN[/red]
      CASE @tblEvalField
      WHEN 'M' THEN 'Month of'
      WHEN 'D' THEN 'Day of'
      WHEN 'Q' THEN 'Quarter ending'
      WHEN 'Y' THEN 'Year ending'
      [red]END[/red]
END

Mantle51,

All you were missing were the parts in red.

But honestly I think a UDF is a poor way to accomplish this. I think you should put this information in a table and join to it. I recognize that the information will likely never change, but I am just a table kind of guy. I hate putting stuff like this into code. I think code should be as generic as possible and not have business terminology in it if it can be accomplished.

CREATE TABLE tblEvalSwitch(
   EvalField char(1) NOT NULL PRIMARY KEY CLUSTERED,
   EvalDescr varchar(20) NOT NULL CONSTRAINT UQ_tblEvalSwitch UNIQUE
)
INSERT tblEvalSwitch Values('M', 'Month of')
INSERT tblEvalSwitch Values('D', 'Day of')
INSERT tblEvalSwitch Values('Q', 'Quarter ending')
INSERT tblEvalSwitch Values('Y', 'Year ending')

SELECT
...
S.EvalDescr
...
FROM
   tblEval E
   INNER JOIN tblEvalSwitch S ON E.EvalField = S.EvalField

If you absolutely MUST have a UDF, make the UDF look up the value from the table.

CREATE FUNCTION fn_tblEvalSwitch (
   @tblEvalField char(1)
) RETURNS varchar(20)
AS  
BEGIN 
   RETURN (SELECT EvalDescr FROM tblEvalSwitch WHERE EvalField = @tblEvalField)
END

Or even better, make it a front-end display issue. Don't twiddle around with the meaning in the back end... ?

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[bborissov]

Sh**
where my brain was?
Sorry Mantle51, that was stupid.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Mantle51]

NO worries bborissov. As a newbie to T-SQL I am appreciative of any and all dialogue.
Many thanks

 

[bborissov]

I must have a rule "Never post anything on Sundays"

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ESquared]

Joining is definitely better. Some people find it hard to think that way, though.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]