I need to pass an array of "id's" to a stored procedure, to delete all rows from the table EXCEPT the rows that match id's in the array.
How can I do it in a most simple way?
I need to pass an array of "id's" to a stored procedure, to delete all rows from the table EXCEPT the rows that match id's in the array.
How can I do it in a most simple way?
you can use this function as a table in a join:
here is your delete:
You could try this:
I like this one, because it is suited to be passed as an XElement, which is suitable for SqlCommand
(Sorry it is VB.NET but you get the idea)
This is the sql Stored proc, shortened, not complete!
CREATE PROCEDURE [dbo].[myproc] (@blah xml)
AS ... WHERE SomeID IN (SELECT doc.t.value('.','int') from @netwerkids.nodes(N'/doc/d') as doc(t))
Use a stored procedure:
EDIT: A complement for serialize List (or anything else):
The result (ready to use with XML parameter):
ORIGINAL POST:
Passing XML as parameter:
If you are using Sql Server 2008 or better, you can use something called a Table-Valued Parameter (TVP) instead of serializing & deserializing your list data every time you want to pass it to a stored procedure.
Let's start by creating a simple schema to serve as our playground:
With our schema and sample data in place, we are now ready to create our TVP stored procedure:
With both our schema and API in place, we can call the TVP stored procedure from our program like so:
There is probably a less painful way to do this using a more abstract API, such as Entity Framework. However, I do not have the time to see for myself at this time.