最近在做开发过程中碰到这么一个纠结的问题,需要同时插入N条数据,不想在程序里控制,但是SQL Sever又不支持数组参数.所以只能用变通的办法了.利用SQL Server强大的字符串处理传把数组格式化为类似"3500320083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750", 然后在存储过程中用SubString配合CharIndex把它分割开来。
stored procedured 如下:
--同时插入N条数据
CREATE PROCEDURE dbo.dis_insertBackStation
@delivery varchar(50), --@usid bigint,
@postationid varchar(50), --@stid varchar(50)
@sn varchar(500)
AS
DECLARE @PointerPrev int
DECLARE @PointerCurr int
DECLARE @TSn varchar(50)
DECLARE @puid varchar(50),@stid varchar(50),@usid varchar(50)
--DECLARE @TId int
Set @PointerPrev=1
set @PointerCurr=1
begin transaction
Set NoCount ON
--delete from ProductListSpecial where ModuleId=@ModuleId
select @usid = usid from usr_users where username = @delivery --'shoulijun'
select @stid = stid from sta_station where postationid = @postationid --5400010240-- postationid --5480011708
--select @puid = puid from dis_war where sn = @sn --这个sn必须要是分解后的
--第一个
Set @PointerCurr=CharIndex('#',@sn,@PointerPrev+1)
set @TSn=cast(SUBSTRING(@sn,@PointerPrev,@PointerCurr-@PointerPrev) as varchar(50))
select @puid = puid from dis_war where sn = @sn --这个sn必须要是分解后的
insert into dis_backstation (bsid,puid,stid,usid,totalmoney,outcash,outmort,incash,ivt,inconvert,createtime) values(NEWID(),@puid,@stid,@usid,0,0,0,0,0,0,GETDATE())
SET @PointerPrev = @PointerCurr
while (@PointerPrev+1 < LEN(@sn))
Begin
Set @PointerCurr=CharIndex('#',@sn,@PointerPrev+1)
if(@PointerCurr>0)
Begin
--中间的
set @TSn=cast(SUBSTRING(@sn,@PointerPrev+1,@PointerCurr-@PointerPrev-1) as varchar(50))
select @puid = puid from dis_war where sn = @sn --这个sn必须要是分解后的
insert into dis_backstation (bsid,puid,stid,usid,totalmoney,outcash,outmort,incash,ivt,inconvert,createtime) values(NEWID(),@puid,@stid,@usid,0,0,0,0,0,0,GETDATE())
SET @PointerPrev = @PointerCurr
End
else
Break
End
--最后的
set @TSn=cast(SUBSTRING(@sn,@PointerPrev+1,LEN(@sn)-@PointerPrev) as varchar(50))
select @puid = puid from dis_war where sn = @sn --这个sn必须要是分解后的
insert into dis_backstation (bsid,puid,stid,usid,totalmoney,outcash,outmort,incash,ivt,inconvert,createtime) values(NEWID(),@puid,@stid,@usid,0,0,0,0,0,0,GETDATE())
Set NoCount OFF
if @@error=0
begin
commit transaction
end
else
begin
rollback transaction
end
GO
执行前查询:
select * from dis_backstation order by createtime
执行:
exec dis_insertBackStation 'songlijun','0540002070','3500320083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750'
查询执行后结果:
select * from dis_backstation order by createtime
对比执行前后的结果,可以发现多了6条记录。此6条即为插入的相关记录。