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