How to set multiple values ​​inside an if else statement?

Question

I am trying to set more than one value in an if else statement below. If I set one value, this works, but if I set two values, it will not 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 next to the keyword "ELSE".

However, it is possible, after the else set, it is possible to have several SET variables; 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 it right?

Answer 1

If you have multiple statements in the 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 code block with multiple statements, similar to using { and } in other languages, where you can place 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 you to assign both in the same statement and therefore avoid using 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' 

Answer 3

If you have multiple statements after IF , you should use begin and end (for example, similar to accolades in C #).

 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 4

Behavior makes sense since your first test will be broken down like this:

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

and ELSE will fail, because it does not belong to any IF expression.

Think about it:

 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 5

The second expression in your question will always lead to the execution of @ test2 = 'testelse' , because ELSE ends immediately after the first expression after else:

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

If you have multiple expressions inside an IF, you can always group expressions using BEGIN / END.

But in your case, the simplest way would be the following:

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