SQL Server Stored Procedure cyclically processes array parameters

Method 1 Segmentation

For example, multiple records are deleted by transmitting array parameters through the SQL server stored procedure.

For example, if the value of ID is '1, 2, 3 ', the following stored procedure is to delete records with IDs of 1, 2, and 3 in the table:

Create procedure deletenews
Id nvarchar (500)
As
Declare @ pointerprev int
Declare @ pointercurr int
Declare @ TID int
Set @ pointerprev = 1

While (@ pointerprev <Len (@ ID ))
Begin
Set @ pointercurr = charindex (',', @ ID, @ pointerprev)
If (@ pointercurr> 0)
Begin
Set @ tid = cast (substring (@ ID, @ pointerprev, @ pointercurr-@ pointerprev) as INT)
Delete from news where Id = @ tid
Set @ pointerprev = @ pointercurr + 1
End
Else
Break
End
-- Delete the last one. Because there is no comma after the last one, it jumps out of the loop and needs to be deleted again.
Set @ tid = cast (substring (@ ID, @ pointerprev, Len (@ ID)-@ pointerprev + 1) as INT)
Delete from news where Id = @ tid
Go

 

Method 2 Table object

Upload three parameters, both in array format and time type updated using Stored Procedure

@ OID = 1, 2, 4

@ Did = 111,222,333,444

@ Datearr = '2017-1-2007-1-1---1-1-4'

 

Create proc test999

@ OID nvarchar (1000) -- id1

, @ Did nvarchar (1000) -- Id2

, @ Datearr nvarchar (1000) -- Date

As

Declare @ id1s varchar (8000), @ id2s varchar (8000), @ dates varchar (8000)

Set @ id1s = @ OID

Set @ id2s = @ did

Set @ dates = @ datearr

-- Call functions for processing

Select @ id1s = @ id1s, @ id2s = @ id2s, @ dates = @ dates

 

Update a set terminate_time = B. dt

From [Table] ,(

Select

Id1 = convert (INT, desk_id.value ),

Id2 = convert (INT, room_id.value ),

Dt = convert (datetime, terminate_time.value)

From DBO. f_splitstr (@ id1s) Route _id, DBO. f_splitstr (@ id2s) room_id, DBO. f_splitstr (@ dates) terminate_time

Where performance_id.id = room_id.id

And pai_id.id = terminate_time.id

) B

Where a. Role _id = B. id1 and A. room_id = B. Id2

Go also uses the f_splitstr function.

Create Function DBO. f_splitstr (

@ STR varchar (8000)

) Returns @ r table (ID int identity (1, 1), value varchar (5000 ))

As

Begin

Declare @ POS int

Set @ Pos = charindex (',', @ Str)

While @ POS> 0

Begin

Insert @ r (value) values (left (@ STR, @ pos-1 ))

Select

@ STR = stuff (@ STR, 1, @ POs ,''),

@ Pos = charindex (',', @ Str)

End

If @ STR>''

Insert @ r (value) values (@ Str)

Return

End

 

Method 3 XML

 

SQL2000 openxml should be simpler and more efficient,CodeMore readable:

Create procedure [DBO]. [productlistupdatespeciallist]
(
@ Productid_array nvarchar (2000 ),
@ Moduleid int
)

As

Delete from productlistspecial whereModuleid = @ 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