Question

存储过程的参数为数组该怎么传递进去

Answer 1

方法一 分割

例:通过SQL Server存储过程传送数组参数删除多条记录

eg. ID 值为'1,2,3' 以下存储过程就是删除表中id号为1,2,3的记录：

CREATE PROCEDURE DeleteNews
@ID nvarchar(500)
as
DECLARE @PointerPrev int
DECLARE @PointerCurr int
DECLARE @TId int
Set @PointerPrev=1

while (@PointerPrev < LEN(@ID))
Begin
Set @PointerCurr=CharIndex(',',@ID,@PointerPrev)
if(@PointerCurr>0)
Begin
set @TId=cast(SUBSTRING(@ID,@PointerPrev,@PointerCurr-@PointerPrev) as int)
Delete from News where ID=@TID
SET @PointerPrev = @PointerCurr+1
End
else
Break
End
--删除最后一个,因为最后一个后面没有逗号,所以在循环中跳出,需另外再删除
set @TId=cast(SUBSTRING(@ID,@PointerPrev,LEN(@ID)-@PointerPrev+1) as int)
Delete from News where ID=@TID
GO

这个方法麻烦不？于是又有另外一种方法——临时表