Rauf Miah (6/23/2013)
table name : mrinffields : companycode nchar(4), mrno (nchar(9), chqno nvarchar (50), tranamount decmal (18,2)
I have to update the several sequential (incremented by 1) mrno to a new squential mrno (incremented by 1)
therefore i have written a stored procedure
CREATE PROCEDURE dbo.changemr (@counter int, @prevmrno nchar(9), @newmrno nchar (9))
AS
BEGIN
SET NOCOUNT ON;
[highlight=#ffff11]Declare @count int
select @count=1
while @count=@counter
select @count = @count+1
select @prevmrno=@prevmrno+1
update mrinf set mrno=@newmrno where comcod=3305 and mrno=@prevmrno
END
GO[/highlight]
but it is not giving the desired result.
For example I am executing the procedure by
use databasename
exec dbo.mrchange 4, '000053345', '000001881'
result should be like this
Previous MR No and new MR NO
000053345 000001881
000053346 000001882
000053347 000001883
000053348 000001884
but not getting any result no change in the table. Can anybody help me to solve this problem.
How many iterations do you think you'll have with this controlling the WHILE loop?
while @count=@counter
According to your example, 0. Go back an look at your code. Step through it with your brain. You'll be able to figure it out. It's just a loop.
Once you've done that, come back and post the code you have working. Then we'll show you how to really simplify this.
--Jeff Moden
Change is inevitable... Change for the better is not.