SQL CASE Expression - Setting values of local variables

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 = @valueELSE IF (@check = 'def') SET @var2 = @valueELSE IF (@check = 'ghi') SET @var3 = @valueELSE IF (@check = 'jkl') SET @var4 = @valueELSE 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).

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   endELSE IF (@check = 'def')    begin       SET @var1 = @value       SET @var2 = @value   endELSE IF (@check = 'ghi')    begin       SET @var1 = @value       SET @var2 = @value   endELSE 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.