Sql Server Case Statement gives me errors in user-defined function

I want to use a case statement in my user defined functions because I need to match on a number of terms. I could use a table for the matches but then I wouldn't be able to put it inside the Computed Column definition.

This works with IF statements:

CREATE FUNCTION MaraSizeNumber
(
    @ms varchar
)
RETURNS varchar
AS
BEGIN
    IF ms = '16-18' RETURN '1'
    ELSE IF ms = '18-20' RETURN '2'
    ELSE IF ms = '20-22' RETURN '3'
    ELSE IF ms = '22+' RETURN '4'
    ELSE IF ms = '24+' RETURN '5'
    ELSE IF ms = '14-16' RETURN '7'
    ELSE RETURN 'BAD'
END

But with the original style using a CASE...WHEN THEN BLOCK I get an error message.

CREATE FUNCTION MaraSizeCaseExample
(
    @ms varchar
)
RETURNS varchar
AS
BEGIN
    CASE ms
    	WHEN '16-18' THEN RETURN '1'
    	WHEN '18-20' THEN RETURN '2'
    	WHEN '20-22' THEN RETURN '3'
    	WHEN '22+' THEN RETURN '4'
    	WHEN '24+' THEN RETURN '5'
    	WHEN '14-16' THEN RETURN '7'
    	ELSE RETURN 'BAD'
    END
END

I get an error of Incorrect Syntax near case and incorrect syntax near when for my when parts.

I have correctly batched everything up, because my last CREATE FUNCTION block ends with the GO, and according to the documentation on CASE, I have the right syntax.

I have a larger scalar function I'm building that will use the other scalar functions to generate the production coding in our system corresponding to other parameters. It would be best to be able to use CASE because the production coding depends on the product and the customer.

I also get an extra error in the second example at my Create Function line that says, "Incorrect Syntax: 'Create Function' must be the only statement in the batch", but with everything else identical I don't get that error with the IFs.

What am I doing wrong, or are CASES only allowed in Sql queries rather than scalar functions? The error messages are coming from Sql Server Management Studio's squiggle error message system.

Answers


The case statement should look like:

RETURN CASE @ms
       WHEN '16-18' THEN '1'
       WHEN '18-20' THEN '2'
       WHEN '20-22' THEN '3'
       WHEN '22+' THEN '4'
       WHEN '24+' THEN '5'
       WHEN '14-16' THEN '7'
       ELSE 'BAD'
  END

Need Your Help

Draw Element's Contents onto a Canvas Element / Capture Website as image using (?) language

image canvas render

I asked a question on SO about compiling an image file from HTML. Michaël Witrant responded and told me about the canvas element and html5.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.