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!