By markiz


2009-07-01 14:08:44 8 Comments

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?

10 comments

@Jovan MSFT 2015-09-23 21:25:29

@Egbert Nierop 2014-06-13 18:05:38

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)

<Extension()>
Public Function ToXml(Of T)(array As IEnumerable(Of T)) As XElement
   Return XElement.Parse(
           String.Format("<doc>{0}</doc>", String.Join("", array.Select(Function(s) String.Concat("<d>", s.ToString(), "</d>")))), LoadOptions.None)
 End Function

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

@Zanoni 2009-07-01 14:21:01

Use a stored procedure:

EDIT: A complement for serialize List (or anything else):

List<string> testList = new List<int>();

testList.Add(1);
testList.Add(2);
testList.Add(3);

XmlSerializer xs = new XmlSerializer(typeof(List<int>));
MemoryStream ms = new MemoryStream();
xs.Serialize(ms, testList);

string resultXML = UTF8Encoding.UTF8.GetString(ms.ToArray());

The result (ready to use with XML parameter):

<?xml version="1.0"?>
<ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <int>1</int>
  <int>2</int>
  <int>3</int>
</ArrayOfInt>

ORIGINAL POST:

Passing XML as parameter:

<ids>
    <id>1</id>
    <id>2</id>
</ids>

CREATE PROCEDURE [dbo].[DeleteAllData]
(
    @XMLDoc XML
)
AS
BEGIN

DECLARE @handle INT

EXEC sp_xml_preparedocument @handle OUTPUT, @XMLDoc

DELETE FROM
    YOURTABLE
WHERE
    YOUR_ID_COLUMN NOT IN (
        SELECT * FROM OPENXML (@handle, '/ids/id') WITH (id INT '.') 
    )
EXEC sp_xml_removedocument @handle

@RolandTumble 2009-07-01 14:44:03

+1 This is a great solution IF your data is already in an XML structure--not so hot when you add the client-side overhead of building the XML.

@markiz 2009-07-01 15:11:13

A RolandTumble mentioned, this will require to convert my iput data array (List) into XML, so i guess it not the best solution in my case.

@Zanoni 2009-07-01 16:05:04

You could use Serialize to do that... It's more reliable use XML than split strings...

@markiz 2009-07-01 17:56:22

Can you please explain how can I serialize List<strings> or arraylist? I don't want to create a wrapper object just for xml serialization.

@markiz 2009-07-01 18:05:44

Serialization also wrapes xml with it's standard header like: xmlns:xsi="w3.org/2001/XMLSchema-instance" is it a problem for sp_xml_preparedocument?

@Zanoni 2009-07-01 18:15:03

@markiz: I complement my post with the serializes...

@markiz 2009-07-05 19:07:47

If instead of serialization, I construct a concatenated string "look a like" xml parameter: string s ="<ids><id>1</id><id>2</id></ids>", will it be more efficient? (I don't need to pass a lot of values)

@user156888 2010-08-12 15:00:28

Just to add to the update here. you need to use xpath to get the list of ints out: SELECT * FROM OPENXML( @xmlHandle, '/ArrayOfInt/int') WITH (id INT '.')

@OBL 2013-01-11 00:05:00

Will this work with strings as well?

@Daniel 2014-01-24 15:34:44

@OBL Yes. This works with strings. However, your Select statement would be more like this: SELECT * FROM OPENXML (@handle, '/ArrayOfString/string') WITH (myString varchar(100) '.') where myString will be the column name and the value will be truncated to the number of characters you list in the varchar declaration.

@Tony Wall 2014-04-02 13:05:09

Have used Table Valued Parameters but this XML method seems like the best way in an SOA/enterprise development environment. Mainly because you will already be dealing a lot with serialization and XML is automatically generated without having to register or build additional types on the database or client side. And it's even easier when you write a generic serialization extension method for any object, using Data Annotations or classic XML serialization attributes to control any specific serialization requirements (or just accepting the defaults which are fine in most cases).

@Vitalivs 2013-04-11 16:21:29

declare @ids nvarchar(1000)

set @ids = '100,2,3,4,5' --Parameter passed

set @ids = ',' + @ids + ','

select   *
from     TableName 
where    charindex(',' + CAST(Id as nvarchar(50)) + ',', @ids) > 0

@wwmbes 2016-09-26 14:34:12

Bingo Vitalivs! First prize is the least code and produces exactly what's required in the least time. Embedded white-spaces can cause trouble in "@ids".

@rmiesen 2012-11-29 17:50:38

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:

CREATE DATABASE [TestbedDb]
GO


USE [TestbedDb]
GO

    /* First, setup the sample program's account & credentials*/
CREATE LOGIN [testbedUser] WITH PASSWORD=N'µ×?
?S[°¿Q­¥½q?_Ĭ¼Ð)3õļ%dv', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

CREATE USER [testbedUser] FOR LOGIN [testbedUser] WITH DEFAULT_SCHEMA=[dbo]
GO

EXEC sp_addrolemember N'db_owner', N'testbedUser'
GO


    /* Now setup the schema */
CREATE TABLE dbo.Table1 ( t1Id INT NOT NULL PRIMARY KEY );
GO

INSERT INTO dbo.Table1 (t1Id)
VALUES
    (1),
    (2),
    (3),
    (4),
    (5),
    (6),
    (7),
    (8),
    (9),
    (10);
GO

With our schema and sample data in place, we are now ready to create our TVP stored procedure:

CREATE TYPE T1Ids AS Table (
        t1Id INT
);
GO


CREATE PROCEDURE dbo.FindMatchingRowsInTable1( @Table1Ids AS T1Ids READONLY )
AS
BEGIN
        SET NOCOUNT ON;

        SELECT Table1.t1Id FROM dbo.Table1 AS Table1
        JOIN @Table1Ids AS paramTable1Ids ON Table1.t1Id = paramTable1Ids.t1Id;
END
GO

With both our schema and API in place, we can call the TVP stored procedure from our program like so:

        // Curry the TVP data
        DataTable t1Ids = new DataTable( );
        t1Ids.Columns.Add( "t1Id",
                           typeof( int ) );

        int[] listOfIdsToFind = new[] {1, 5, 9};
        foreach ( int id in listOfIdsToFind )
        {
            t1Ids.Rows.Add( id );
        }
        // Prepare the connection details
        SqlConnection testbedConnection =
                new SqlConnection(
                        @"Data Source=.\SQLExpress;Initial Catalog=TestbedDb;Persist Security Info=True;User ID=testbedUser;Password=letmein12;Connect Timeout=5" );

        try
        {
            testbedConnection.Open( );

            // Prepare a call to the stored procedure
            SqlCommand findMatchingRowsInTable1 = new SqlCommand( "dbo.FindMatchingRowsInTable1",
                                                                  testbedConnection );
            findMatchingRowsInTable1.CommandType = CommandType.StoredProcedure;

            // Curry up the TVP parameter
            SqlParameter sqlParameter = new SqlParameter( "Table1Ids",
                                                          t1Ids );
            findMatchingRowsInTable1.Parameters.Add( sqlParameter );

            // Execute the stored procedure
            SqlDataReader sqlDataReader = findMatchingRowsInTable1.ExecuteReader( );

            while ( sqlDataReader.Read( ) )
            {
                Console.WriteLine( "Matching t1ID: {0}",
                                   sqlDataReader[ "t1Id" ] );
            }
        }
        catch ( Exception e )
        {
            Console.WriteLine( e.ToString( ) );
        }
  /* Output:
   * Matching t1ID: 1
   * Matching t1ID: 5
   * Matching t1ID: 9
   */

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.

@user1466918 2012-11-29 22:23:44

I think this is the best way to do this. I am shocked you got no votes for this. So I gave you one.

@Justin 2013-04-04 21:29:43

I like this approach. Thanks for sharing.

@mrmillsy 2013-08-23 14:34:31

For reusability, I believe this is by far the best approach. Consider the following common real-world application: I have a search, which generates a result set including a unique primary key. I then wish to pass that resultset through to another operation (eg, retrieving futher information for an excel spreadsheet). With this method, I can easily retrieve a list of IDs from the initial resultset and pass the list into my second phase stored procedure which accepts a single parameter of "@IDs T1IDs READONLY". Thereby I sidestep the heavy load of the initial search.

@Simon 2009-07-01 15:04:59

You could use a temp table which the stored procedure expects to exist. This will work on older versions of SQL Server, which do not support XML etc.

CREATE TABLE #temp
(INT myid)
GO
CREATE PROC myproc
AS
BEGIN
    DELETE YourTable
    FROM YourTable                    
    LEFT OUTER JOIN #temp T ON T.myid=s.id
    WHERE s.id IS NULL
END

@Adriaan Stander 2009-07-06 11:03:08

You could try this:



DECLARE @List VARCHAR(MAX)

SELECT @List = '1,2,3,4,5,6,7,8'

EXEC(
'DELETE
FROM TABLE
WHERE ID NOT IN (' + @List + ')'
)

@Joe 2014-04-10 10:47:18

... as long as you're sure the @List parameter isn't populated from user input

@wwmbes 2016-09-26 15:49:03

Beauty is simplicity.

@KM. 2009-07-01 14:10:45

this is the best source:

http://www.sommarskog.se/arrays-in-sql.html

create a split function using the link, and use it like:

DELETE YourTable
    FROM YourTable                           d
    LEFT OUTER JOIN dbo.splitFunction(@Parameter) s ON d.ID=s.Value
    WHERE s.Value IS NULL

I prefer the number table approach

This is code based on the above link that should do it for you...

Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @[email protected]+1
    INSERT INTO Numbers VALUES (@x)
END

use this function to split your string, which does not loop and is very fast:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn              char(1)              --REQUIRED, the character to split the @List string on
    ,@List                 varchar(8000)        --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
    ListValue varchar(500)
)
AS
BEGIN

/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.


Returns a table, one row per item in the list, with a column name "ListValue"

EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')

    returns:
        ListValue  
        -----------
        1
        12
        123
        1234
        54321
        6
        A
        *
        |||
        B

        (10 row(s) affected)

**/



----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
        (ListValue)
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''



RETURN

END --Function FN_ListToTable

you can use this function as a table in a join:

SELECT
    Col1, COl2, Col3...
    FROM  YourTable
        INNER JOIN dbo.FN_ListToTable(',',@YourString) s ON  YourTable.ID = s.ListValue

here is your delete:

DELETE YourTable
    FROM YourTable                                d
    LEFT OUTER JOIN dbo.FN_ListToTable(',',@Parameter) s ON d.ID=s.ListValue
    WHERE s.ListValue IS NULL

@Pandincus 2010-12-15 17:13:02

@KM: Why the maximum of 8000 characters? Can we use strings longer than that?

@KM. 2010-12-16 13:31:43

you can make the input parameter varchar(max), just be sure your Numbers table has enough rows. I just use 8000, because I never split long strings. You can see this other answer of mine: stackoverflow.com/questions/4227552/… where I have updated the split function to use a table function and also improved the Numbers table generation.

@Scott Ivey 2009-07-01 14:17:15

I'd consider passing your IDs as an XML string, and then you could shred the XML into a temp table to join against, or you could also query against the XML directly using SP_XML_PREPAREDOCUMENT and OPENXML.

@Cody C 2009-07-01 14:14:49

What about using the XML data type instead of passing an array. I find that a better solution and works well in SQL 2005

@markiz 2009-07-01 14:17:24

Can you please provide code + explanation?

@thijs 2009-07-01 14:22:03

reading through xml is slow compared to splitting strings!

@Zanoni 2009-07-01 16:01:45

You trust to use splitted strings? Trust first than speed...

@KM. 2009-07-06 19:16:10

@Zanoni, why not trust split strings?

Related Questions

Sponsored Content

18 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

5 Answered Questions

[SOLVED] SQL Server SELECT into existing table

39 Answered Questions

[SOLVED] Parameterize an SQL IN clause

16 Answered Questions

[SOLVED] Select columns from result set of stored procedure

10 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

22 Answered Questions

[SOLVED] Search text in stored procedure in SQL Server

7 Answered Questions

25 Answered Questions

9 Answered Questions

[SOLVED] How to pass an array into a SQL Server stored procedure

15 Answered Questions

[SOLVED] SQL Server - SELECT FROM stored procedure

Sponsored Content