WEB开发网
开发学院数据库MSSQL Server 为SQL Server传数组参数的变通办法 阅读

为SQL Server传数组参数的变通办法

 2007-05-31 15:23:06 来源:WEB开发网   
核心提示:最近一直在做Dnn模块的开发,过程中碰到这么一个问题,需要同时插入N条数据,不想在程序里控制,但是SQL Sever又不支持数组参数.所以只能用变通的办法了.利用SQL Server强大的字符串处理传把数组格式化为类似"1,2,3,4,5,6", 然后在存储过程中用SubString配合CharI
最近一直在做Dnn模块的开发,过程中碰到这么一个问题,需要同时插入N条数据,不想在程序里控制,但是SQL Sever又不支持数组参数.所以只能用变通的办法了.利用SQL Server强大的字符串处理传把数组格式化为类似"1,2,3,4,5,6"。
  
   然后在存储过程中用SubString配合CharIndex把分割开来
  
   详细的存储过程
  
   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
  
   网友Bizlogic对此的改进方法:
  
   应该用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

Tags:SQL Server 数组

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接