CODEWITHSUNDEEP
sql-servert-sql
Bobby
Bobby

Reputation: 2928

SQL Server : set a variable in CASE statement

Is it possible to set a variable in a case statement? My code doesn't work. Thanks all.

CREATE PROCEDURE spReport
@q1 INT,
@q2 INT
AS
BEGIN

-- Dates for the 2 different quarters to be viewed
DECLARE @StartDateQ1 DATETIME
DECLARE @EndDateQ1 DATETIME
DECLARE @StartDateQ2 DATETIME
DECLARE @EndDateQ2 DATETIME

SELECT
CASE @q1
    WHEN 1 THEN SET @StartDateQ1 = '20130401'
END

Upvotes: 41

Views: 171875

Answers (2)

Tarzan
Tarzan

Reputation: 4538

Here's another way to solve this using an IF statement:

IF @q1 = 1
BEGIN
    SET @StartDateQ1 = '20130401'
END

Upvotes: 5

Martin Smith
Martin Smith

Reputation: 452988

You can use

SET @StartDateQ1 = CASE @q1
                     WHEN 1 THEN '20130401'
                   END

to set the value of a single variable according to a CASE expression.

If your real logic is more complicated (e.g. need to set multiple variables inside a condition) look at IF ... ELSE instead.

CASE is an expression not a flow of control construct.

Upvotes: 78

Related Questions