This should do what you need. BUT please, read this article and work on getting rid of the WHILE loop.

The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]

CREATE PROCEDURE dbo.changemr

@counter INT

,@prevmrno NCHAR(9)

,@newmrno NCHAR(9)

,@comcod INT

AS

BEGIN

/*

EXEC dbo.changemr 4, '000053345', '000001881', 3305

*/

SET NOCOUNT ON

DECLARE

@intCount INT

,@intPrev INT

,@intNew INT

,@strPrev NCHAR(9)

,@strNew NCHAR(9)

SET @intCount = 1

WHILE @intCount <= @counter

BEGIN

SET @intPrev = CAST(@prevmrno AS INT)+1

SET @intNew = CAST(@newmrno AS INT)+1

SET @strPrev = REPLICATE('0',9-LEN(@intPrev))+CAST(@intprev AS NVARCHAR(10))

SET @strNew = REPLICATE('0',9-LEN(@intNew))+CAST(@intNew AS NVARCHAR(10))

UPDATE mrinf

SET mrno = @newmrno

WHERE

comcod = @comcod

AND mrno = @prevmrno

SET @prevmrno = @strPrev

SET @newmrno = @strNew

SET @intCount = @intCount + 1

END

END