CODEWITHSUNDEEP
sql-servert-sql
Jackal
Jackal

Reputation: 3521

Use set in a case statement in SQL Server

I would like to set a variable in SQL Server using a CASE statement. For example:

DECLARE @UNITY VARCHAR(5)
DECLARE @AUX VARCHAR(5)

CASE
    WHEN @UNITY = 'U1' THEN @AUX = 'M1'
    WHEN @UNITY = 'U2' THEN @AUX = 'M2'
    WHEN @UNITY = 'U3' THEN @AUX = 'M3'
END

Upvotes: 0

Views: 4602

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

You can't use case as a flow control. An SQL case is an expression that return a scalar value based on condition(s).
It's well documented in the remarks section:

The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures. For a list of control-of-flow methods, see Control-of-Flow Language (Transact-SQL).

A working code would be written like this:

DECLARE @UNITY VARCHAR(5)
DECLARE @AUX VARCHAR(5)

SET @AUX = 
CASE @UNITY
    WHEN 'U1' THEN 'M1'
    WHEN 'U2' THEN 'M2'
    WHEN 'U3' THEN 'M3'
END

Note I'm using the Simple CASE expression syntax for brevity.

Upvotes: 5

Related Questions