How to pass an array parameter to a stored procedure?

It was easy to meet this requirement. Who knows that SQL does not support arrays. So I want to use ',' to split the data and pass it in. Which one knows that SQL does not actually have the split () function? I have to use substring & charindex.

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 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

 

Is this method troublesome? So there is another method-temporary table

 

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

 

GoThis 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

This method is even more terrible ~~~ Baidu found a good method. With openxml, this SQL2000 is supported.

 

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

Wow, it looks complicated. Is there a better way?

Since it is openxml, why not use XML? As a result, sql2005 and above all support xml. Good, I found a day.

The XML/XQuery function of sql2005 can easily solve the problem of passing array parameters.

 

 

Declare @ XML
Set @ xml = '<? XML version = "1.0"?>
<Arrayofint>
<Int> 1 </int>
<Int> 2 </int>
<Int> 3 </int>
</Arrayofint>'

 

Select N. Value ('(text () [1]', 'int') roomid from @ XML. nodes ('/arrayofint/int') V (N)

The result is

 

Note: The above data type is XML

In this way, you can pass a set to the stored procedure, which is generally an array, such as a set of primary keys. You can then use the primary key set to query records.

The client can use serialization to convert the list into XML. However, there are some minor problems in the serialization process.

1. Net default is UTF-16, SQL only know UTF-8

2. Appearing annoying xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance" xmlns: XSD = "http://www.w3.org/2001/XMLSchema"

You can use this class

 Public   Static  Class  Serializehelper {  Private   Static   Readonly Xmlserializernamespaces namespaces = New  Xmlserializernamespaces ();  Static  Serializehelper (){  //  Remove xmlns: xsi ="  Http://www.w3.org/2001/XMLSchema-instance  "Xmlns: XSD =" Http://www.w3.org/2001/XMLSchema  " Namespaces. Add ( String . Empty, String  . Empty );}  Public   Static   String Serializexml <t> (T obj) {xmlserializer serializer = New Xmlserializer ( Typeof  (T )); Using (Memorystream stream = New  Memorystream () {serializer. serialize (stream, OBJ, namespaces );  Return  Encoding. utf8.getstring (stream. toarray ());}}  Public   Static T deserializexml <t> ( String  OBJ) {xmlserializer serializer = New Xmlserializer ( Typeof (T ));  Using (Stringreader reader = New  Stringreader (OBJ )){  Return  (T) serializer. deserialize (Reader );}}} 

The combination of SQL, XML, and XQuery provides powerful functions.

We can also see from this event that I should pay more attention to the new functions between different SQL versions, such as the new functions of sql2008. Otherwise, a better solution is not found.

OK. The problem is solved in a perfect way. The best way is to solve the problem. Thank you ~~~ O (partition _ partition) O!