SQL CASE expression - setting local variable values

Question

I have T-SQL code using multiple if statements (about 100) as shown below. If the first condition of the IF statement is TRUE, it still evaluates the remaining 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 them to use a CASE expression. eg,

 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 cannot do this, and I get an SQL error that says that I cannot use SET in a CASE expression.

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

Answer 1

The case statement will not trim it: the only way to use SET with CASE is:

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

... which will not work for you, as this can only set one variable. Therefore, 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 have 100 sentences, such as this parameter, 100 different variables, then it looks like your approach might be wrong: I would take a step back and ask myself why you need 100 separate variables. Perhaps something is better than what you can do as a set-based solution, either reading from the main or temporary tables. However, we will need more detailed information about what you are trying to do (with a small but fully working example).

Answer 2

As an amendment to Chris J. Anyone who wants to set MULTIPLE parameters inside one 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 

Pay attention to the use of the "begin" and "end" operators. These keywords are similar to braces found in most programming languages, and allow you to specify multiple lines in this statement.