Workarounds for SQL Server-pass array parameters

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