sql调用存储过程传入数组方法

最新推荐文章于 2024-08-28 15:37:17 发布

lhh1113

最新推荐文章于 2024-08-28 15:37:17 发布

阅读量3.1k

点赞数

本文链接：https://blog.csdn.net/lhh1113/article/details/78598398

版权

sqlserver 调用存储过程在传入参数的时候不能传入数组，所以一般将数据作为字符串

比如表格：

--方法一(用xml来拆分字符串)
declare @str varchar(1000)
declare @index int
set @index = 100
set @str = '10,20,30'


  
insert into [QPTreasureDB].[dbo].[RedPacketRandomData] select [RedPaceteIndex]
      ,[Score]
      ,[IsReceieved]   From 
 (
Select RedPaceteIndex, CAST( '<v>'+REPLACE(Score,',','</v><v>')+'</v>' as xml) As xml,IsReceieved 
From (select  @index As RedPaceteIndex,@str As Score,0 As IsReceieved) S
 ) a outer apply ( Select x.y.value('.','varchar(100)') as Score 
From a.xml.nodes('/v') x(y)) b