CASE in a UDF

microsoft.public.sqlserver.programming

11 Dec 2006 5:06 PM

ricky

Hi Everyone

Is it possible to use a CASE statement, instead of an IF statement in a UDF.
The current IF UDF is as follows:

ALTER FUNCTION dbo.ClassBusiness_NU_IncomeAnalysis (@ProductCode AS
VARCHAR(6),@Sectiontype AS VARCHAR(10))

RETURNS VARCHAR(10) AS

BEGIN
DECLARE @TempClass varchar(10)

IF @Sectiontype = 'EL' OR @Sectiontype = 'PRL' OR @Sectiontype = 'POL'
   BEGIN
    SET @TempClass = 'Liability'
   END

ELSE

IF @Sectiontype = 'TRR'
   BEGIN
    SET @TempClass = 'Terrorism'
   END

ELSE
   SET @TempClass = 'Property'

RETURN (@TempClass)

END

--*****************************************************

I have tried re-writing this as:

CREATE FUNCTION fn_Temp (@Code AS VARCHAR(2))

RETURNS VARCHAR(10) AS
BEGIN

DECLARE @TempClass varchar(10)

CASE
WHEN @Code = 'EL' OR @Code = 'PRL' OR @Code = 'POL' THEN
   SET @TempClass = 'Liability'

WHEN @Code = 'TRR' THEN
   SET @TempClass = 'Terrorism'

WHEN @Code = 'LE' OR @Code = 'BC' OR @Code = 'CGO' OR @Code = 'CST' THEN
   SET @TempClass = 'Property'
END

RETURN (@TempClass)

END

But I seem to get errors regarding the "WHEN" keyword, am I missing
something out here?