SQL Server存储过程数组参数

SQL Server存储过程数组参数

 

 SQL Server存储过程不支持数组参数类型，要传递数量不定的参数，需要采用其他方式进行传递

 

方法一：利用字符串截取拆分数组字符串

-- =============================================

-- Author:<lx>

-- Create date: <2010-11-26>

-- Description:    <存储过程数组参数>

-- 方法一:利用字符串截取拆分数组字符串

-- =============================================

CREATE PROCEDURE SP_ParameterArray 

 @UserName VARCHAR(10),

 @StrArray VARCHAR(800)

AS

DECLARE @P INT

DECLARE @C INT

DECLARE @Nums INT

SET @P=1 

SET @C=1

BEGIN TRANSACTION     

Set NOCOUNT ON

 CREATE TABLE #t_info

 (

   UserName VARCHAR(10),

   Nums INT

 )

 SET @C=CHARINDEX(',',@StrArray,@P+1)

 SET @Nums=CAST(SUBSTRING(@StrArray,@P,@C-@P) AS INT)

 INSERT INTO #t_info(UserName,Nums) VALUES(@UserName,@Nums)

 SET @P=@C

 WHILE (@P+1< LEN(@StrArray))

  BEGIN

   SET @C=CHARINDEX(',',@StrArray,@P+1)

   IF(@C> 0)

    BEGIN

     SET @Nums=CAST (SUBSTRING(@StrArray,@P+1,@C-@P-1) AS INT)

     INSERT INTO #t_info(UserName,Nums) VALUES(@UserName,@Nums)

     SET @P=@C

    END

   ELSE

    BREAK  

  END

SET @Nums=CAST(SUBSTRING(@StrArray,@P+1,LEN(@StrArray)-@P) AS INT)

INSERT INTO #t_info(UserName,Nums) VALUES(@UserName,@Nums)

SELECT * FROM #t_info

DROP TABLE #t_info

SET NOCOUNT OFF

IF @@ERROR=0

 BEGIN

  COMMIT TRANSACTION

 END

ELSE

 BEGIN

   ROLLBACK TRANSACTION

 END

GO

 

方法二：利用OpenXML 效率更高

-- =============================================

-- Author:<lx>

-- Create date: <2010-11-26>

-- Description:    <存储过程数组参数>

-- 方法二:利用OpenXML 效率更高

-- @XML_Array='<Array><PA Nums="1"></PA><PA Nums="2"></PA><PA Nums="3"></PA><PA Nums="4"></PA></Array>'

-- =============================================

CREATE PROCEDURE [dbo].[SP_ParameterArray2] 

 @UserName VARCHAR(10),

 @XML_Array nvarchar(500)

AS

BEGIN 

IF (@XML_Array IS NULL OR LEN(LTRIM(RTRIM(@XML_Array))) = 0) 

 RETURN 

CREATE TABLE #t_info(UserName VARCHAR(10),Nums INT)

DECLARE @IdHandel INT

EXEC sp_xml_preparedocument @IdHandel OUTPUT, @XML_Array

INSERT INTO #t_info(UserName,Nums)

SELECT @UserName,N.Nums 

FROM OPENXML(@IdHandel,'/Array/PA') WITH(Nums INT) AS N

WHERE N.[Nums] IS NOT NULL

EXEC sp_xml_removedocument @IdHandel

SELECT * FROM #t_info

DROP TABLE #t_info

END

转载：http://www.2cto.com/database/201304/203136.html

下面为您介绍的SQL Server删除方法和一般的SQL Server删除方法有所不同，该方法实现的是在SQL Server存储过程通过传送数组字符串参数SQL Server删除多条记录（如多选或全选表单中的多选框所获取的一组数值删除 ）。

    
    
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   
 
 
set ANSI_NULLS ON  
set QUOTED_IDENTIFIER ON  
GO  
 
CREATE PROCEDURE AddInter   
    @userID nvarchar(max),  
@ProjecID int  
as   
begin  
    DECLARE @PointerPrev int   
    DECLARE @PointerCurr int   
    DECLARE @TId int   
    Set @PointerPrev=1   
    begin transaction   
    while (@PointerPrev < LEN(@userID))   
    Begin   
        Set @PointerCurr=CharIndex(',',@userID,@PointerPrev)   
        if(@PointerCurr>0)   
        Begin   
            set @TId=cast(SUBSTRING(@userID,@PointerPrev,@PointerCurr-@PointerPrev) as int)      
            exec AddIntention @TID,@ProjecID,0,2    
            SET @PointerPrev = @PointerCurr+1   
        End   
        else   
            Break   
    End   
    --删除最后一个,因为最后一个后面没有逗号,所以在循环中跳出,需另外再删除   
     set @TId=cast(SUBSTRING(@userID,@PointerPrev,LEN(@userID)-@PointerPrev+1) as int)   
exec AddIntention @TID,@ProjecID,0,2  
IF (@@error <> 0)  
   begin     
    ROLLBACK TRANSACTION   
   end  
 
COMMIT TRANSACTION  
Return   
end        
GO  

转载：http://database.51cto.com/art/201010/231033.htm

   最近在做开发过程中碰到这么一个纠结的问题,需要同时插入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条即为插入的相关记录。

转载：http://blog.csdn.net/ylchou/article/details/7279980

方法一 分割

例:通过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

 

方法二 Table对象

传3个参数，都是数组形式还有时间类型用存储过程更新

@Oid = 1,2,3,4

@Did = 111,222,333,444

@DateArr = '2007-1-1,2007-1-2,2007-1-3,2007-1-4'

 

CREATE proc Test999

@Oid nvarchar(1000)    --ID1

,@Did nvarchar(1000)   --ID2

,@DateArr nvarchar(1000) --日期

AS

DECLARE @id1s varchar(8000), @id2s varchar(8000), @dates varchar(8000)

set @id1s=@Oid       

set @id2s=@Did       

set @dates = @DateArr

-- 调用函数实现处理

SELECT @id1s=@id1s, @id2s=@id2s,@dates = @dates

 

UPDATE A SET terminate_time = B.dt

FROM [Table] A,(

SELECT

    id1 = CONVERT(int, Desk_id.value),

    id2 = CONVERT(int, room_id.value),

    dt = CONVERT(datetime, terminate_time.value)

FROM dbo.f_splitstr(@id1s) Desk_id, dbo.f_splitstr(@id2s) room_id, dbo.f_splitstr(@dates) terminate_time

WHERE Desk_id.id = room_id.id

    AND Desk_id.id = terminate_time.id

) B

WHERE A.Desk_id = B.ID1 AND A.room_id = B.ID2

GO这个还用到一个函数f_splitstr

CREATE FUNCTION dbo.f_splitstr(

    @str varchar(8000)

)RETURNS @r TABLE(id int IDENTITY(1, 1), value varchar(5000))

AS

BEGIN

    DECLARE @pos int

    SET @pos = CHARINDEX(',', @str)

    WHILE @pos > 0

    BEGIN

        INSERT @r(value) VALUES(LEFT(@str, @pos - 1))

        SELECT

            @str = STUFF(@str, 1, @pos, ''),

            @pos = CHARINDEX(',', @str)

    END

    IF @str > ''

        INSERT @r(value) VALUES(@str)

    RETURN

END

 

方法三 xml

 

应该用SQL2000 OpenXML更简单，效率更高，代码更可读：

CREATE Procedure [dbo].[ProductListUpdateSpecialList] 
( 
@ProductId_Array NVARCHAR(2000), 
@ModuleId INT 
)

AS

delete from ProductListSpecial where ModuleId=@ModuleId

-- If empty, return 
IF (@ProductId_Array IS NULL OR LEN(LTRIM(RTRIM(@ProductId_Array))) = 0) 
RETURN

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @ProductId_Array

Insert into ProductListSpecial (ModuleId,ProductId) 
Select 
@ModuleId,C.[ProductId] 
FROM 
OPENXML(@idoc, '/Products/Product', 3) 
with (ProductId int ) as C 
where 
C.[ProductId] is not null

EXEC sp_xml_removedocument @idoc