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 [email protected]_gsbh,[email protected]_xmbh
insert into zwdlcs2006 select * from zwdlcs_gao
update zwdllm_gao set [email protected]_gsbh
insert into zwdllm2006 select * from zwdllm_gao
update zwzygs_gao set [email protected]_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 [email protected]_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)[email protected] as varchar),3)
set @par12=right( '0000 '+cast(cast( '0406 ' as int)[email protected] as varchar),4)
update zwdlcs_gao set [email protected],[email protected]
insert into zwdlcs2006 select * from zwdlcs_gao
update zwdllm_gao set [email protected]
insert into zwdllm2006 select * from zwdllm_gao
update zwzygs_gao set [email protected]
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 [email protected]
end