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

Question

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.

Answer 1

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

Answer 2

I think your problem is because you are not specifying the size on your varchar (which is defaulting to 1). This is resulting in the input and the output being truncated to a single character. (You were also missing an @ on your variable inside the function.)

So PRINT dbo.SizeExample('16-18') will print 'B' (which is not only truncated, it's also not the answer you probably want, which is '1')

Try instead:

CREATE FUNCTION MaraSizeExample
(
    @ms varchar(5)
)
RETURNS varchar(3)
AS
BEGIN
    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
END

1. CASE is just a searched (or lookup) expression - you cannot RETURN from inside it - it's kind of like like IIF() in VB or IF() in Excel. The earlier answer basically explained that.

2. VARCHAR means VARCHAR(1), so you would get truncation - i.e. you'd never see 'BAD' coming out, only 'B'. I'm not sure why you don't get a truncation warning when it happens like you get on some SQL INSERT, but there you go.

3. @ms is your variable's name. I'm not sure why ms would be accepted unless there was some other database object by that name (a table?) - and even then it wouldn't be. All variables and parameters in T-SQL are prefixed with @ like this.