create proc sp_test (@inum int)
AS
declare @i int
set @i = 0
while @i< @inum
begin
sql語句1
sql語句2
set @i = @i + 1
end
sql語句3
sql語句4
sql語句5
AS
declare @i int
set @i = 0
while @i< @inum
begin
sql語句1
sql語句2
set @i = @i + 1
end
sql語句3
sql語句4
sql語句5
CREATE PROCEDURE [dbo].[spInsertPrDirClothes]
(
@DirControlNo decimal,
@DirControlDetailNo int,
@BranchCD int
)
其中下边要执行Insert操作,
有两个表的主键是上面三个,前两个不变的前提下对第三个BranchCD进行循环。
外部传值的时候传(3,4,5,6,7)这样的形式。
我要问的就是怎么把branchcd的值分别插入。
牵扯的就是在存储过程中的循环问题,谢谢
create procedure #IntListToTable
@cslist varchar(8000),
@tablename Sysname
AS
Begin
Declare @spot smallint, @str varchar(8000), @sql varchar(8000)
while @cslist < > ' '
Begin
Set @spot = charindex( ', ', @cslist)
if @spot > 0
Begin
Set @str = cast(left(@cslist, @spot-1) as int)
set @cslist = right(@cslist, len(@cslist)-@spot)
end
else
Begin
Set @str = cast(@cslist as int)
Set @cslist = ' '
End
Set @sql = 'Insert Into ' + @tablename + ' Values( '+convert(varchar(100),@str)+ ') '
exec(@sql)
end
endcreate table #vals (id INT)exec #IntListToTable '30000,50000,8000,2000,3000 ', '#vals 'select id from #vals结果:30000
50000
8000
2000
3000