Workarounds to pass array parameters to SQL Server databases

Recently has been doing DNN module development, the process encountered such a problem, you need to insert n data, do not want to control in the program, but the SQL sever does not support array parameters. So it's only a workaround. Use SQL Server's powerful string handling to format the array like " 1,2,3,4,5,6 ".

And then split the substring with the charindex in the stored procedure.

Detailed Stored Procedures

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

Netizen Bizlogic to this improvement method:

You should use SQL2000 OPENXML simpler, more efficient, and more readable code:

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