[转]Sql Server 存储过程传入数组参数的处理

原文：http://www.cnblogs.com/jeffreyQ/archive/2011/09/08/2171696.html

ALTER  PROCEDURE proc_TaskJumpPriority

(
     @ID varchar(100)--多条作业ID如：10，11,12  
)
 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)
                PRINT @TId
                SET @PointerPrev = @PointerCurr+1
            END
        ELSE
            BREAK
    END
    --因为最后一个ID后面没有逗号，所以在跳出循环后获取
    SET @TId = cast(substring(@ID,@PointerPrev,LEN(@ID)-@PointerPrev+1) as int)
    print @TId
GO
--exec proc_TaskJumpPriority '10,20,30'

 

原文：http://9527.cnblogs.com/archive/2005/09/04/230000.html

CREATE PROCEDURE dbo.ProductListUpdateSpecialList
    @ProductId_Array varChar(800),
    @ModuleId int
AS
    DECLARE @PointerPrev int
    DECLARE @PointerCurr int
    DECLARE @TId int
    Set @PointerPrev=1
    set @PointerCurr=1
    
    begin transaction
    Set NoCount ON
    delete  from ProductListSpecial where ModuleId=@ModuleId
    
    Set @PointerCurr=CharIndex(',',@ProductId_Array,@PointerPrev+1)
    set @TId=cast(SUBSTRING(@ProductId_Array,@PointerPrev,@PointerCurr-@PointerPrev) as int)
    Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId)
    SET @PointerPrev = @PointerCurr
    while (@PointerPrev+1 < LEN(@ProductId_Array))
    Begin
        Set @PointerCurr=CharIndex(',',@ProductId_Array,@PointerPrev+1)
        if(@PointerCurr>0)
        Begin
            set @TId=cast(SUBSTRING(@ProductId_Array,@PointerPrev+1,@PointerCurr-@PointerPrev-1) as int)
            Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId)
            SET @PointerPrev = @PointerCurr
        End
        else
            Break
    End
    
    set @TId=cast(SUBSTRING(@ProductId_Array,@PointerPrev+1,LEN(@ProductId_Array)-@PointerPrev) as int)
    Insert into ProductListSpecial (ModuleId,ProductId) Values(@ModuleId,@TId)
    Set NoCount OFF
    if @@error=0
    begin
        commit transaction
    end
    else
    begin
        rollback transaction
    end
GO

 

 方法示例：

declare @x varchar(20) --必须为可变字符串
select @x='meg,good,hello,dell' --例子,以逗号分隔
declare @n int --逗号的位置

select @n=charindex(',',@x) --取出第一个逗号的位置
while @n>0 --即存在逗号
    begin
        select @n=charindex(',',@x)
        if @n<> 0
        begin
            print substring(@x,1,@n-1) --这个就是要取的字符串
--print @x
--print @n
            select @x=right(@x,len(@x)-@n) --截取上次逗号后面的字符串。
        end
        else --已经没有逗号了，只剩一个单词了
            print @x --这个就是要取的字符串
    end

 

 方法二：http://blog.csdn.net/bigcarp/article/details/456440

假如我有数组:a(1)=111,a(2)=222,a(3)=333……
w我要把数组传入存储过程，但SQLSERVER不支持数组，搜索baidu，找到一个很强的方法：
1、先把数组转成一个以逗号分隔的字符串a='111,222,333,444,……'
2、把字符串转换成一个用union连起来的select语句：
      select '111' as a union select '222' as a union select '333' as a union select '444' as a……
则此语句可以得到以下结果：
a
----------
111
222
333
444

然后把结果插入到临时表中：
declare @string varchar(80)
declare @sqlSTR2TABLE varchar(1000)

set @string='111,222,333,444,555,666,777,888'

set @sqlSTR2TABLE='select ''' + replace (@string,',',''' as caller_id union select ''') + ''''
exec(@sqlSTR2TABLE)