How SQL Sever inserts n data at the same time without controlling in the program

Q: "How to insert n data at the same time, but not in the program control?" ”

A: "Because the SQL Sever does not support array parameters. So we can only use alternative methods. Use SQL Server powerful string handling to format the array as" 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