Function with CASE Statements

Hi,

Uses: SQL Server 2000 + Winxp PRO;

I Created a Function as below:

<CODE>
CREATE FUNCTION [GetDestinationOperator] (@.Dest VARCHAR(24))
RETURNS VARCHAR(15)
AS
BEGIN

DECLARE @.Op VARCHAR(15)

SET @.Dest = RTRIM(@.Dest)

CASE LEN(@.Dest)
WHEN 13 THEN SET @.Op = 'IDD'
WHEN 10 THEN
CASE SUBSTRING (@.Dest , 1 , 3 )
WHEN '071' THEN SET @.Op = 'MOBITEL'
WHEN '072' THEN SET @.Op = 'CELTEL'
WHEN '077' THEN SET @.Op = 'DIALOG'
WHEN '078' THEN SET @.Op = 'HUTCH'
ELSE SET @.Op = 'NATIONAL'
END
WHEN 7 THEN
CASE SUBSTRING (@.Dest , 1 , 1 )
WHEN '2' THEN SET @.Op = 'SLT'
WHEN '4' THEN SET @.Op = 'SUNTEL'
WHEN '5' THEN SET @.Op = 'LANKA BELL'
ELSE SET @.Op = 'LOCAL'
END
WHEN 3 THEN SET @.Op = 'INTERNAL'
ELSE SET @.Op = 'UNKNOWN'
END

RETRUN @.Op

END
<\CODE>

However when I checked the Code I written I get error Message as saying:

Error = 156:
Incorrect Syntax near the Keyword CASE
Incorrect Syntax near the Keyword WHEN
.
.
.

However when I executed the Query which I used with my database mentioned below, I get no errors:

<CODE>

SELECT CASE LEN(CalledNo)
WHEN 13 THEN 'IDD'
WHEN 10 THEN
CASE SUBSTRING (CalledNo , 1 , 3 )
WHEN '071' THEN 'MOBITEL'
WHEN '072' THEN 'CELTEL'
WHEN '077' THEN 'DIALOG'
WHEN '078' THEN 'HUTCH'
ELSE 'NATIONAL'
END
WHEN 7 THEN
CASE SUBSTRING (CalledNo , 1 , 1 )
WHEN '2' THEN 'SLT'
WHEN '4' THEN 'SUNTEL'
WHEN '5' THEN 'LANKA BELL'
ELSE 'LOCAL'
END
WHEN 3 THEN 'INTERNAL'
ELSE 'UNKNOWN'
END, CalledNo
FROM PABX
WHERE ExtNo = 204

<\CODE>

What might be the problem here?

Regards,

Hifni

Can you try this
CREATE FUNCTION GetDestinationOperator (@.Dest VARCHAR(24))
RETURNS VARCHAR(15)
AS
BEGIN

DECLARE @.Op VARCHAR(15)

SET @.Dest = RTRIM(@.Dest)
select @.op=
CASE LEN(@.Dest)
WHEN 13 THEN 'IDD'
WHEN 10 THEN
CASE SUBSTRING (@.Dest , 1 , 3 )
WHEN '071' THEN 'MOBITEL'
WHEN '072' THEN 'CELTEL'
WHEN '077' THEN 'DIALOG'
WHEN '078' THEN 'HUTCH'
ELSE 'NATIONAL'
END
WHEN 7 THEN
CASE SUBSTRING (@.Dest , 1 , 1 )
WHEN '2' THEN 'SLT'
WHEN '4' THEN 'SUNTEL'
WHEN '5' THEN 'LANKA BELL'
ELSE 'LOCAL'
END
WHEN 3 THEN 'INTERNAL'
ELSE 'UNKNOWN'
END

RETURN @.Op

END|||The case statement cannot stand alone even within a fuction. Create a variable called @.result and modify your fuction to Select @.result = <your case statement> then return the result.|||

Replace the CASE statment with a series of

IF condition

BEGIN

...

END

That should work

|||CASE is an expression not a control of flow statement. So you need to either assign the variable @.op using the value of the CASE expression or use IF...ELSE statements. Also, you should avoid writing scalar UDFs like this which perform lookup operations and use it in SELECT statements. You will get sub-optimal performance. It is based to model the lookup in a table and then perform a simple SELECT operation. This also gives added flexibility and performs better. Or you can inline the expression in the UDF in a view or TVF and use that instead.|||

Hi Everyone,
Thanks for all of your responses. For Eisa, I tried your sql and it did work wery well. Thanks for it and Umarchandra who gave a technical detail of it, thanks as well and for the rest.

Regards,