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删除多条记录(如多选或全选表单中的多选框所获取的一组数值删除 )。

    
  1. CREATE PROCEDURE DeleteNews   
  2.     @ID nvarchar(500)   
  3. as   
  4.     DECLARE @PointerPrev int   
  5.     DECLARE @PointerCurr int   
  6.     DECLARE @TId int   
  7.     Set @PointerPrev=1   
  8.        
  9.     while (@PointerPrev < LEN(@ID))   
  10.     Begin   
  11.         Set @PointerCurr=CharIndex(',',@ID,@PointerPrev)   
  12.         if(@PointerCurr>0)   
  13.         Begin   
  14.             set @TId=cast(SUBSTRING(@ID,@PointerPrev,@PointerCurr-@PointerPrev) as int)   
  15.             Delete from News where ID=@TID   
  16.             SET @PointerPrev = @PointerCurr+1   
  17.         End   
  18.         else   
  19.             Break   
  20.     End   
  21.     --删除最后一个,因为最后一个后面没有逗号,所以在循环中跳出,需另外再删除   
  22.      set @TId=cast(SUBSTRING(@ID,@PointerPrev,LEN(@ID)-@PointerPrev+1) as int)   
  23.      Delete from News where ID=@TID   
  24. GO   
  25.  
  26.  
  27. set ANSI_NULLS ON  
  28. set QUOTED_IDENTIFIER ON  
  29. GO  
  30.  
  31. CREATE PROCEDURE AddInter   
  32.     @userID nvarchar(max),  
  33. @ProjecID int  
  34. as   
  35. begin  
  36.     DECLARE @PointerPrev int   
  37.     DECLARE @PointerCurr int   
  38.     DECLARE @TId int   
  39.     Set @PointerPrev=1   
  40.     begin transaction   
  41.     while (@PointerPrev < LEN(@userID))   
  42.     Begin   
  43.         Set @PointerCurr=CharIndex(',',@userID,@PointerPrev)   
  44.         if(@PointerCurr>0)   
  45.         Begin   
  46.             set @TId=cast(SUBSTRING(@userID,@PointerPrev,@PointerCurr-@PointerPrev) as int)      
  47.             exec AddIntention @TID,@ProjecID,0,2    
  48.             SET @PointerPrev = @PointerCurr+1   
  49.         End   
  50.         else   
  51.             Break   
  52.     End   
  53.     --删除最后一个,因为最后一个后面没有逗号,所以在循环中跳出,需另外再删除   
  54.      set @TId=cast(SUBSTRING(@userID,@PointerPrev,LEN(@userID)-@PointerPrev+1) as int)   
  55. exec AddIntention @TID,@ProjecID,0,2  
  56. IF (@@error <> 0)  
  57.    begin     
  58.     ROLLBACK TRANSACTION   
  59.    end  
  60.  
  61. COMMIT TRANSACTION  
  62. Return   
  63. end        
  64. 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

相关推荐
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页