Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
 Posted - 2007-10-19 : 09:24:14
|
| I'm debugging a stored procedure of mine. here's the basic gist of the code I'm concerned about:Declare @Num1 intDeclare @Num2 intDeclare @Num3 intDeclare @Num_var varchar (10)Declare @String varchar (5) -- Set using queryDeclare @Value varchar (10) -- Set using queryWhile @@FETCH_STATUS = 0BEGIN-- Result of query is inserted into Fetch statement. The query -- itself returns something on the order of 1000x rows; so, let's say-- the result is 10000 rows for argument sake. Consequently,-- the value of @value may or may not be different on each pass-- of the while loop. @String may also change, as well. Fetch Next from Some_Cursor Into @Value, @Coverage, etc...SET @Num1 = CASE WHEN @String = 'ABC' then @Value END;SET @Num2 = CASE WHEN @String = 'DEF' then @Value END;SET @Num3 = CASE WHEN @String = 'GHI' then @Value END;SET @Num_var = CASE WHEN @String = 'JKL' then @Value END;...END -- repeat loopOK, the problem is on each iteration of the while loop, a previously stored @Num value will be assigned NULL. So, on the first pass, @Value is 500 and @String is ABC, then @Num1 = 500. On the second iteration of the while loop, the @String may or may not be differnt but let's say it's equal to GHI. Nonetheless, regardless of what value is stored in @String, on this second iteration, @Num1 is assigned NULL. The same will ring true for the other Num values except Num3, which will be assigned the value of @value. Now, obviously, all the @Num values start out as NULL. But what I am trying to accomplish is I only want one of the @Num values to be set on each pass, not all four. If a Num value on a previous pass is 300, it should stay 300 unless it's case condition is met and it's changed to another number like 400.; none of them shouldn't be assigned NULL or remain NULL at the completion of the while loop. Now, when the while loop is completed and I've iterated through the query table, row-by-row, 3 of the Num values will be set to null and the other one will contain a value and this is obviously not what I want. I can accomplish this fine in an if statment, one would imagine. But I wanted to use case instead. How can I fix my case statement so that all the num values are properly set and do not reset on each pass of the while loop? |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2007-10-19 : 09:30:08
|
| I know, but for conceptual purposes, is it impossible to carry out this exact task with the CASE conditional?Thank you. |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2007-10-19 : 11:03:58
|
| Yep, that'll work. Great. I suppose there's a hidden else condition of NULL with the manner in which I setup the case statement then...Thank you! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2007-10-19 : 13:10:16
|
| OK. Thank you again. |
|
|
|
|
|
|
|
|
values in case statements change to null in while