By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,341 Members | 1,553 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,341 IT Pros & Developers. It's quick & easy.

Why is there an incorrect syntax near the equal sign?

100+
P: 103
I can not figure out why I am getting an error. Can someone help? Here is my code with the line with issue highlighted.
Expand|Select|Wrap|Line Numbers
  1. SET @var1 = ''
  2. SET @var2 = ''
  3. SET @var3 = ''
  4. SET @var4 = ''
  5.  
  6. SELECT @Counter = MIN(ID) FROM #TEMP WHERE ID>0
  7.  
  8. WHILE @Counter > 0
  9. BEGIN
  10. SELECT @key = Phase FROM #TEMP WHERE ID = @Counter
  11. SELECT (CASE WHEN @Key = 1 THEN
  12.          @Var1 = (SELECT Agency FROM #TEMP WHERE ID = @Counter)
This is my whole query for the part I need help with.
Expand|Select|Wrap|Line Numbers
  1. DECLARE @Counter AS INT
  2. DECLARE @key AS INT
  3. DECLARE @var1 AS INT
  4. DECLARE @var2 AS INT
  5. DECLARE @var3 AS INT
  6. DECLARE @var4 AS INT
  7.  
  8. SET @var1 = ''
  9. SET @var2 = ''
  10. SET @var3 = ''
  11. SET @var4 = ''
  12.  
  13. SELECT @Counter = MIN(ID) FROM #TEMP WHERE ID>0
  14.  
  15. WHILE @Counter > 0
  16. BEGIN
  17. SELECT @key = Phase FROM #TEMP WHERE ID = @Counter
  18. SELECT (CASE WHEN @Key = 1 THEN
  19.          @Var1 = (SELECT Agency FROM #TEMP WHERE ID = @Counter)
  20.          @var2 = SELECT Request FROM #TEMP WHERE ID = @Counter
  21.          @var3 = SELECT SourceofFunds FROM #TEMP WHERE ID = @Counter
  22.  
  23.         UPDATE #TEMP
  24.         SET Agency = @Var1
  25.         WHERE Request = @var2 AND SourceofFunds = @var3 AND ID <> @Counter END)
  26.  
  27. END 
  28. SELECT @Counter = MIN(ID) FROM #TEMP WHERE ID>@Counter
  29. END
Feb 8 '13 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,768
You seem to be treating your variables as both Integer (INT) and string. An INT variable cannot sensibly hold an empty string ('') value.
Feb 9 '13 #2

Rabbit
Expert Mod 10K+
P: 12,430
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.
Feb 9 '13 #3

deepuv04
Expert 100+
P: 227
change your loop as
Expand|Select|Wrap|Line Numbers
  1. WHILE @Counter > 0
  2. BEGIN
  3.  
  4.     SELECT @key = Phase FROM #TEMP WHERE ID = @Counter
  5.  
  6.     IF @Key = 1
  7.     BEGIN 
  8.         SELECT 
  9.              @Var1 =  Agency ,
  10.              @var2 =  Request,
  11.              @var3 =  SourceofFunds FROM #TEMP WHERE ID = @Counter
  12.  
  13.         UPDATE    #TEMP
  14.         SET        Agency = @Var1
  15.         WHERE    Request = @var2 AND SourceofFunds = @var3 AND ID <> @Counter 
  16.     END
  17.  
  18.     SELECT @Counter = MIN(ID) FROM #TEMP WHERE ID>@Counter
  19. END
  20.  
Feb 11 '13 #4

Post your reply

Sign in to post your reply or Sign up for a free account.