Workaround for transferring array parameters to SQL Server

server| Array

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 ". Then in the stored procedure with the substring with charindex to separate.

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