SQL CASE Expression - Setting values of local variables

Question

I have some T-SQL code using multiple if statements (about 100) as below. If the first IF statement condition evaluates to TRUE it still evaluates the rest of 99 statements.

IF(@check = 'abc') SET @var1 = @value
IF(@check = 'def') SET @var2 = @value
IF(@check = 'ghi') SET @var3 = @value
IF(@check = 'jkl') SET @var4 = @value
IF(@check = 'mno') SET @var5 = @value
…
…

I want to convert these to use a CASE Expression. for e.g

CASE @check
    WHEN 'abc' THEN SET @var1 = @value
    WHEN 'def' THEN SET @var2 = @value
    WHEN 'ghi' THEN SET @var3 = @value
    WHEN 'jkl' THEN SET @var4 = @value
    WHEN 'mno' THEN SET @var5 = @value
    …
    …
END

However, I am not able to do so, and I get a SQL error that says I cannot use SET within a CASE Expression.

Does anyone have any ideas how I can achieve this? Thanks!

Presentable

You can use else if, but, each of those conditions is setting a different variable. Are you sure you typed the question correctly? – Argile

Will it work SET @var1 = CASE WHEN ... THEN ... END ? – Thea

Why do you need 100 different variables? Why not just assign to one variable based on the value of @check? – Hartfield

Yes, the question is typed correctly. There's a 100 different variables. And each of these variables value is inserted into a corresponding column into a TABLE. – Presentable

If you only set one and the other 99 are NULL... again, I think there are better ways to do this than defining 100 variables. – Hartfield

This code is within a CURSOR (fast_forward read only) While loop so it sets values for all variables, but in different iterations – Presentable

@Presentable ... do you have a simple example of what the entire sproc is doing (we don't want the entire sproc -- just something simple & short so we know what's going on). If you're trying to optimise for performance, the getting rid of the cursor is going to have a bigger effect than optimising adding a lot of IF...ELSE. – Southbound

Answer 1

A case statement won't cut it: the only way you can use SET with CASE is to do:

SET @var = CASE @check
       WHEN 'abc' THEN @value
       [etc]
    END

...which won't work for you as this can only set a single variable. So you need to use ELSE, i.e.:

IF (@check = 'abc') SET @var1 = @value
ELSE IF (@check = 'def') SET @var2 = @value
ELSE IF (@check = 'ghi') SET @var3 = @value
ELSE IF (@check = 'jkl') SET @var4 = @value
ELSE IF (@check = 'mno') SET @var5 = @value
   [...]

However if you've got 100 clauses like this setting 100 different variables then it sounds like your approach may be wrong: I'd take a step back and ask yourself why you need 100 separate variables. There may be something better you can do as a set-based solution, either reading from core or temporary tables. However we'd need more details of what you're trying to do (with a small, but fully working, example).

Southbound

+1, especially for taking a step back. If you're trying to do something like this in SQL, you probably took a wrong turn about five steps ago... – Quint

I would rather not write 100 nested ELSE IF statements. This SQL code is in some old stored procedures not written by me. I am trying to optimize it for performance. – Presentable

and it's SQL Server 2000 stored procedure – Presentable

Often the only way to optimise for performance is to nuke it from orbit and rewrite. It's the only way to be sure. I've lost count of how many times I've had to do exactly this... – Southbound

Answer 2

As an ammendment to Chris J's answer. Anyone here looking to set MULTIPLE parameters within a single if, else if or else -- use the following syntax:

IF (@check = 'abc') 
   begin
       SET @var1 = @value
       SET @var2 = @value
   end
ELSE IF (@check = 'def') 
   begin
       SET @var1 = @value
       SET @var2 = @value
   end
ELSE IF (@check = 'ghi') 
   begin
       SET @var1 = @value
       SET @var2 = @value
   end
ELSE IF (@check = 'jkl') 
   begin
       SET @var1 = @value
       SET @var2 = @value
   end
 ELSE IF (@check = 'mno') 
   begin
       SET @var1 = @value
       SET @var2 = @value
   end

Notice the use of "begin" and "end" statements. These keywords are analogous to the curly braces found in most programming languages and allow for multiple lines to be specified in a given statement.

Janeth