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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-05 : 00:59:12
|
Bev writes "Hi there,
I am having trouble with a varible I am using in a stored procedure. I only copied the troublesome code here to minimize confusion. The basic idea: replace the replaceable parameters in an HTML string with the values (R1..R5) stored in the table, and then update the row. Problem. The replace works fine the first time through, but the second time through, the same values are used (so @x still is equalling "1"). So I printed out @x (i.e. BEGIN Select @x) and this value increments. However, in my next statement that uses the @x, the value is still "1" ((SELECT R1 FROM @Opp WHERE OppID = @x )). I can't seem to figure this out...
How can the variable lose its value from one line to the next?
I half hope you find this difficult (since you like hard questions) and half hope there is a simple fix.
Please let me know if you see what I am doing wrong.
Any help is much appreciated. Perhaps I'm going about this the wrong way?
Thanks, Bev
/* STEP 5: SET variable required for Outer and Inner loops */
SET @rowcount = (SELECT COUNT(*) FROM @Opp)
SET @x = 1
SET @r = 1
/* STEP 6: LOOP through records and perform replace */
/* Outermost loop */
WHILE @x < @Rowcount + 1
BEGIN
/* Innermost loop */
WHILE @r < 6
BEGIN
Select @x
IF @r = 1
SET @value1 = (SELECT R1 FROM @Opp WHERE OppID = @x )
IF @r = 2
SET @value2 = (SELECT R2 FROM @Opp WHERE OppID = @x )
If @r = 3
SET @value3 = (SELECT R3 FROM @Opp WHERE OppID = @x )
If @r = 4
SET @value4 = (SELECT R4 FROM @Opp WHERE OppID = @x )
If @r = 5
SET @value5 = (SELECT R5 FROM @Opp WHERE OppID = @x )
SET @r = @r + 1
END
SET @r = 1
If Len(@value1) > 0
SET @html = REPLACE( @html, '', @value1)
If Len(@value2) >0
SET @html = REPLACE( @html, '', @value2)
If Len(@value3) > 0
SET @html = REPLACE( @html, '', @value3)
If Len(@value4) > 0
SET @html = REPLACE( @html, '', @value4)
If Len(@value5) > 0
SET @html = REPLACE( @html, '', @value5)
Update @Opp
SET Message = @html
WHERE OppID = @x
SET @x = @x + 1
END
Select * from @Opp" |
|
|
|
|
|
|
|
variable not holding value while looping