SQLTeam.com | Weblogs | Forums

IF Statement in Variable


#1

Hello community,

Could someone please help with the syntax in below script? I am trying to set a variable with a IF statement but I am getting an error. This syntax causes the IF to be underlined in red and also the last parenthesis at the end of the statement is underlined in red as well. Thanks in advance. I appreciate any help.

DECLARE @Variable INTEGER
SET @Variable = (IF (SELECT SUM([Amount])
FROM [Table]
WHERE Condition1 = @Condition1 AND Condition2 = @Condtition2) > 0
SELECT 01
ELSE
SELECT - 2)

This is the error being displayed;

Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near ')

I tried removing the open parenthesis before IF and the close parenthesis after -2, then only I get the error message below.
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'IF'.


#2

Try:

DECLARE @Variable INTEGER
 IF (SELECT SUM([Amount])
 FROM [Table]
 WHERE Condition1 = @Condition1 AND Condition2 = @Condtition2) > 0 -- missing variable declaration for @Condition1 an d @condition2
 SELECT @Variable = 1;
 ELSE 
 SELECT @Variable = - 2 ;

#3

Thank you very much, Joseph Torre! This worked beautifully.


#4
SET @Variable = CASE WHEN (SELECT SUM([Amount]) FROM [Table] 
        WHERE Condition1 = @Condition1 AND Condition2 = @Condition2) > 0 
    THEN 01 ELSE -2 END

#5

Thank you, ScottPletcher. This is very helpful. Much appreciated.