How to transmit array parameters to SQL Server

I have been developing the dnn module recently. I encountered such a problem that I need to insert n data records at the same time. Program But SQL Sever does not support array parameters. Therefore, you can only use a work und. use SQL Server's powerful string processing to format the array as "1, 2, 3, 4, 5, 6 ". In the stored procedure, use substring and charindex to separate them.

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 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 should be simpler and more efficient,CodeMore readable:

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