怎样写存储过程??
update   zwdlcs_gao   set   zwdlcs_gsbh= '006 ',zwdlcs_xmbh= '0406 '
insert   into   zwdlcs2006   select   *   from   zwdlcs_gao

update   zwdllm_gao   set   zwdllm_gsbh= '006 '
insert   into   zwdllm2006   select   *   from   zwdllm_gao

update   zwzygs_gao   set   zwzygs_gsbh= '006 '
insert   into   zwzygs2006   select   *   from   zwzygs_gao
UPDATE   zwzygs2006
      SET   zwzygs_gsmc   = '库存材料 '+ '( '+lshsxm2006.lshsxm_xmmc+ ') '
      FROM   lshsxm2006   INNER   JOIN   zwzygs2006  
            ON   lshsxm2006.lshsxm_xmbh   =   '0406 '
      WHERE   zwzygs2006.zwzygs_dwbh= '0104 '
            AND   zwzygs2006.zwzygs_gsbh= '006 '

上面的 '006 '是递增的有限制. '0406 '是递增的有限制.
如果gsbh= '007 ',那么xmbh= '0407 '
如果规定gsbh是从001到055,xmbh是从0401到0455,
那么这个存储过程怎么写??急!多谢帮忙!

------解决方案--------------------
try


Create ProceDure SP_TEST(@Start Int, @End Int)
As
Begin
Declare @I Int, @zwdlcs_gsbh Varchar(10),@zwdlcs_xmbh Varchar(10)
Select @I = @Start
While @I <= @End
Begin
Select @zwdlcs_gsbh = Right(1000 + @I, 3), @zwdlcs_xmbh = '04 ' + Right(100 + @I, 2)

update zwdlcs_gao set zwdlcs_gsbh=@zwdlcs_gsbh,zwdlcs_xmbh=@zwdlcs_xmbh
insert into zwdlcs2006 select * from zwdlcs_gao

update zwdllm_gao set zwdllm_gsbh=@zwdlcs_gsbh
insert into zwdllm2006 select * from zwdllm_gao

update zwzygs_gao set zwzygs_gsbh=@zwdlcs_gsbh
insert into zwzygs2006 select * from zwzygs_gao
UPDATE zwzygs2006
SET zwzygs_gsmc = '库存材料 '+ '( '+lshsxm2006.lshsxm_xmmc+ ') '
FROM lshsxm2006 INNER JOIN zwzygs2006
ON lshsxm2006.lshsxm_xmbh = @zwdlcs_xmbh
WHERE zwzygs2006.zwzygs_dwbh= '0104 '
AND zwzygs2006.zwzygs_gsbh=@zwdlcs_gsbh

Select @I = @I + 1
End
End
GO
EXEC SP_TEST 1, 55
------解决方案--------------------
传入一个参数进行循环的话
declare @i int --循环次数
declare @par1 varchar(20)--参数一
declare @par2 varchar(20)--参数二
set @i=0
while @i <54-- '要循环的值本例中五十五次 '
begin
set @par1=right( '000 '+cast(cast( '006 ' as int)+@i as varchar),3)
set @par12=right( '0000 '+cast(cast( '0406 ' as int)+@i as varchar),4)
update zwdlcs_gao set zwdlcs_gsbh=@par1,zwdlcs_xmbh=@par12
insert into zwdlcs2006 select * from zwdlcs_gao

update zwdllm_gao set zwdllm_gsbh=@par1
insert into zwdllm2006 select * from zwdllm_gao

update zwzygs_gao set zwzygs_gsbh=@par1
insert into zwzygs2006 select * from zwzygs_gao

UPDATE zwzygs2006
SET zwzygs_gsmc = '库存材料 '+ '( '+lshsxm2006.lshsxm_xmmc+ ') '
FROM lshsxm2006 INNER JOIN zwzygs2006
ON lshsxm2006.lshsxm_xmbh = @par2
WHERE zwzygs2006.zwzygs_dwbh= '0104 '
AND zwzygs2006.zwzygs_gsbh=@par1
end