Why is there an incorrect syntax near the equal sign?

[Brian Connelly]

I can not figure out why I am getting an error. Can someone help? Here is my code with the line with issue highlighted.

SET @var1 = ''
SET @var2 = ''
SET @var3 = ''
SET @var4 = ''
 
SELECT @Counter = MIN(ID) FROM #TEMP WHERE ID>0
 
WHILE @Counter > 0
BEGIN
SELECT @key = Phase FROM #TEMP WHERE ID = @Counter
SELECT (CASE WHEN @Key = 1 THEN
         @Var1 = (SELECT Agency FROM #TEMP WHERE ID = @Counter)

This is my whole query for the part I need help with.

DECLARE @Counter AS INT
DECLARE @key AS INT
DECLARE @var1 AS INT
DECLARE @var2 AS INT
DECLARE @var3 AS INT
DECLARE @var4 AS INT
 
SET @var1 = ''
SET @var2 = ''
SET @var3 = ''
SET @var4 = ''
 
SELECT @Counter = MIN(ID) FROM #TEMP WHERE ID>0
 
WHILE @Counter > 0
BEGIN
SELECT @key = Phase FROM #TEMP WHERE ID = @Counter
SELECT (CASE WHEN @Key = 1 THEN
         @Var1 = (SELECT Agency FROM #TEMP WHERE ID = @Counter)
         @var2 = SELECT Request FROM #TEMP WHERE ID = @Counter
         @var3 = SELECT SourceofFunds FROM #TEMP WHERE ID = @Counter
 
        UPDATE #TEMP
        SET Agency = @Var1
        WHERE Request = @var2 AND SourceofFunds = @var3 AND ID <> @Counter END)
 
END 
SELECT @Counter = MIN(ID) FROM #TEMP WHERE ID>@Counter
END

[NeoPa]

You seem to be treating your variables as both Integer (INT) and string. An INT variable cannot sensibly hold an empty string ('') value.

[Rabbit]

Get rid of the SELECT on line 18. You may also need a begin and end to surround your then clause in your case statement.

Usually, when you only have one condition, I just use the IF instead of CASE.

[deepuv04]

change your loop as

WHILE @Counter > 0
BEGIN
 
    SELECT @key = Phase FROM #TEMP WHERE ID = @Counter
 
    IF @Key = 1
    BEGIN 
        SELECT 
             @Var1 =  Agency ,
             @var2 =  Request,
             @var3 =  SourceofFunds FROM #TEMP WHERE ID = @Counter
 
        UPDATE    #TEMP
        SET        Agency = @Var1
        WHERE    Request = @var2 AND SourceofFunds = @var3 AND ID <> @Counter 
    END
 
    SELECT @Counter = MIN(ID) FROM #TEMP WHERE ID>@Counter
END