How to set multiple values inside an if else statement?

Question

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?

Answer 1

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 

Answer 2

Use BEGIN and END to mark a multi-statement block of code, much like using { and } in other languages, in which you can place your multiple SET statements...

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 

Or, use SELECT to assign values to your variables, allowing both to be assigned in a single statement and so avoid requiring the use of BEGIN and END.

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