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
         [B][U]@Var1 = (SELECT Agency FROM #TEMP WHERE ID = @Counter)[/U][/B]

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