How to set multiple values inside an if else statement?
SqlSql ServerTsqlSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
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'
Solution 3 - Sql
If you have multiple statements after the IF
you have to use begin
and end
(similar to accolades in c#, for example).
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
Solution 4 - Sql
The behavior makes sense since your first trial will be split 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 since it does not belong to any IF statement.
Consider doing like this:
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
Solution 5 - Sql
The second expression in your question will always result execute @test2 = 'testelse' because the ELSE ends right after the first expression after the 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 within the IF, you can always group the expressions using BEGIN / END.
But in your case, the most simple way would be this:
IF (SELECT COUNT(*) FROM table WHERE table.Date > '2016-03-20') > 10
SELECT @test1 = 'test1', @test2 = 'test2'
ELSE
SELECT @test1 = 'testelse', @test2 = 'testelse'