Passing an array of parameters to a stored procedure

Question

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

User · Answer

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

User · Answer

Use a stored procedure   EDIT  A complement for serialize List  or anything else    List lt string gt  testList   new List lt int gt      testList Add 1   testList Add 2   testList Add 3    XmlSerializer xs   new XmlSerializer typeof List lt int gt     MemoryStream ms   new MemoryStream    xs Serialize ms  testList    string resultXML   UTF8Encoding UTF8 GetString ms ToArray       The result  ready to use with XML parameter     lt  xml version  1 0   gt   lt ArrayOfInt xmlns xsi  http   www w3 org 2001 XMLSchema-instance  xmlns xsd  http   www w3 org 2001 XMLSchema  gt     lt int gt 1 lt  int gt     lt int gt 2 lt  int gt     lt int gt 3 lt  int gt   lt  ArrayOfInt gt      ORIGINAL POST   Passing XML as parameter    lt ids gt       lt id gt 1 lt  id gt       lt id gt 2 lt  id gt   lt  ids gt      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

User · Answer

You could use the STRING SPLIT function in SQL Server  You can check the documentation here   DECLARE  YourListOfIds VARCHAR 1000  -- Or VARCHAR MAX  depending on what you need  SET  YourListOfIds    1 2 3 4 5 6 7 8   SELECT   FROM YourTable WHERE Id IN SELECT CAST Value AS INT  FROM STRING SPLIT  YourListOfIds

User · Answer

If you are using Sql Server 2008 or better  you can use something called a Table-Valued Parameter  TVP  instead of serializing  amp  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  amp  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

User · Answer

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    lt Extension   gt  Public Function ToXml Of T  array As IEnumerable Of T   As XElement    Return XElement Parse             String Format   lt doc gt  0  lt  doc gt    String Join     array Select Function s  String Concat   lt d gt    s ToString      lt  d gt        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

User · Answer

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 lt 8000 BEGIN     SET  x  x 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  lt  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

User · Answer

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

User · Answer

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

User · Answer

In SQL Server 2016 you can wrap array with     and pass it as JSON see http   blogs msdn com b sqlserverstorageengine archive 2015 09 08 passing-arrays-to-t-sql-procedures-as-json aspx

User · Answer

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

User · Answer

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

[sql] Passing an array of parameters to a stored procedure

Examples related to sql

Examples related to sql-server-2005

Examples related to stored-procedures