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!
How to use CASE to set a variable value in SQL Server? To set a variable value in SQL server based on true / false result, CASE statements can be used. In above case, the value of @pdId is 31 that exists in the Accounts table and the value of the @isExists variable is set to 'Accounts details exists'.
Or you can assign a single row-single column SELECT statement's result to a variable by the SET keyword: SET @YourVariable = (SELECT COUNT(1) FROM YourTable). You can not mix the above options. Furthermore, CTE is defined within the execution scope of a single SELECT , INSERT , UPDATE , or DELETE statement.
Even on a SQL Server instance installed with a binary collation, variable and parameter data types are case insensitive, so you can do this just fine: CREATE PROCEDURE dbo.
Global variables are pre-defined system variables. It starts with @@. It provides information about the present user environment for SQL Server. SQL Server provides multiple global variables, which are very effective to use in Transact-SQL.
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).
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With