Skip to content
Advertisement

How to set multiple values inside an if else statement?

I’m trying to SET more than one value within the if else statement below, If I set one value it works, but if I set two values, it doesn’t work:

DECLARE @test1 varchar(60);
DECLARE @test2 varchar(60);


IF ((SELECT COUNT(*) FROM table WHERE table.Date > '2016-03-20') > 10)
SET @test1 = 'test1'
SET @test2 = 'test2' 
ELSE
SET @test1 = 'testelse'
SET @test2 = 'testelse'

Error message: “Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword ‘ELSE’.”

However it seems to be possible to have multiple SET variables after the else; this code works:

IF ((SELECT COUNT(*) FROM table WHERE table.Date > '2016-03-20') > 10)
SET @test1 = 'test1'
ELSE
SET @test1 = 'testelse'
SET @test2 = 'testelse'

How can I do this correctly?

Advertisement

Answer

If you have more than one statement in a if condition, you must use the BEGIN ... END block to encapsulate them.

IF ((SELECT COUNT(*) FROM table WHERE table.Date > '2016-03-20') > 10)
BEGIN
 SET @test1 = 'test1'
 SET @test2 = 'test2' 
END
ELSE
BEGIN
 SET @test1 = 'testelse'
 SET @test2 = 'testelse'
END
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement