为SQLServer数据库传数组参数的变通办法

　最近一直在做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

　　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