How to pass an array into a SQL Server stored procedure

Question

How to pass an array into a SQL Server stored procedure   For example  I have a list of employees  I want to use this list as a table and join it with another table  But the list of employees should be passed as parameter from C

User · Answer

You need to pass it as an XML parameter   Edit  quick code from my project to give you an idea   CREATE PROCEDURE  dbo   GetArrivalsReport       DateTimeFrom AS DATETIME       DateTimeTo AS DATETIME       HostIds AS XML xsdArrayOfULong  AS BEGIN     DECLARE  hosts TABLE  HostId BIGINT       INSERT INTO  hosts         SELECT arrayOfUlong HostId value      bigint   data         FROM  HostIds nodes   arrayOfUlong u   as arrayOfUlong HostId    Then you can use the temp table to join with your tables  We defined arrayOfUlong as a built in XML schema to maintain data integrity  but you don t have to do that  I d recommend using it so here s a quick code for to make sure you always get an XML with longs   IF NOT EXISTS  SELECT   FROM sys xml schema collections WHERE name    xsdArrayOfULong   BEGIN     CREATE XML SCHEMA COLLECTION  dbo   xsdArrayOfULong      AS N  lt xs schema xmlns xs  http   www w3 org 2001 XMLSchema  gt       lt xs element name  arrayOfUlong  gt           lt xs complexType gt               lt xs sequence gt                   lt xs element maxOccurs  unbounded                              name  u                              type  xs unsignedLong    gt               lt  xs sequence gt           lt  xs complexType gt       lt  xs element gt   lt  xs schema gt    END GO

User · Answer

Use a table-valued parameter for your stored procedure   When you pass it in from C  you ll add the parameter with the data type of SqlDb Structured   See here  http   msdn microsoft com en-us library bb675163 aspx  Example       Assumes connection is an open SqlConnection object  using  connection       Create a DataTable with the modified rows  DataTable addedCategories     CategoriesDataTable GetChanges DataRowState Added       Configure the SqlCommand and SqlParameter  SqlCommand insertCommand   new SqlCommand       usp InsertCategories   connection   insertCommand CommandType   CommandType StoredProcedure  SqlParameter tvpParam   insertCommand Parameters AddWithValue        tvpNewCategories   addedCategories   tvpParam SqlDbType   SqlDbType Structured      Execute the command  insertCommand ExecuteNonQuery

User · Answer

It took me a long time to figure this out  so in case anyone needs it     This is based on the SQL 2005 method in Aaron s answer  and using his SplitInts function  I just removed the delim param since I ll always use commas    I m using SQL 2008 but I wanted something that works with typed datasets  XSD  TableAdapters  and I know string params work with those   I was trying to get his function to work in a  where in  1 2 3   type clause  and having no luck the straight-forward way   So I created a temp table first  and then did an inner join instead of the  where in    Here is my example usage  in my case I wanted to get a list of recipes that don t contain certain ingredients   CREATE PROCEDURE dbo SOExample1            excludeIngredientsString varchar MAX             AS        Convert string to table of ints        DECLARE  excludeIngredients TABLE  ID int      insert into  excludeIngredients     select ID   Item from dbo SplitInts  excludeIngredientsString          Select recipies that don t contain any ingredients in our excluded table       SELECT        r Name  r Slug FROM            Recipes AS r LEFT OUTER JOIN                          RecipeIngredients as ri inner join                           excludeIngredients as ei on ri IngredientID   ei ID                          ON r ID   ri RecipeID WHERE         ri RecipeID IS NULL

User · Answer

CREATE TYPE dumyTable AS TABLE     RateCodeId int    RateLowerRange int    RateHigherRange int    RateRangeValue int    GO CREATE PROCEDURE spInsertRateRanges    dt AS dumyTable READONLY AS BEGIN   SET NOCOUNT ON     INSERT  tblRateCodeRange RateCodeId RateLowerRange RateHigherRange RateRangeValue     SELECT      FROM  dt  END

User · Answer

SQL Server 2008  or newer   First  in your database  create the following two objects   CREATE TYPE dbo IDList AS TABLE     ID INT    GO  CREATE PROCEDURE dbo DoSomethingWithEmployees    List AS dbo IDList READONLY AS BEGIN   SET NOCOUNT ON     SELECT ID FROM  List   END GO   Now in your C  code      Obtain your list of ids to send  this is just an example call to a helper utility function int   employeeIds   GetEmployeeIds     DataTable tvp   new DataTable    tvp Columns Add new DataColumn  ID   typeof int         populate DataTable from your List here foreach var id in employeeIds      tvp Rows Add id    using  conn        SqlCommand cmd   new SqlCommand  dbo DoSomethingWithEmployees   conn       cmd CommandType   CommandType StoredProcedure      SqlParameter tvparam   cmd Parameters AddWithValue   List   tvp          these next lines are important to map the C  DataTable object to the correct SQL User Defined Type     tvparam SqlDbType   SqlDbType Structured      tvparam TypeName    dbo IDList          execute query  consume results  etc  here     SQL Server 2005  If you are using SQL Server 2005  I would still recommend a split function over XML  First  create a function   CREATE FUNCTION dbo SplitInts       List      VARCHAR MAX       Delimiter VARCHAR 255    RETURNS TABLE AS   RETURN   SELECT Item   CONVERT INT  Item  FROM         SELECT Item   x i value     text    1     varchar max            FROM   SELECT  XML    CONVERT XML    lt i gt             REPLACE  List   Delimiter    lt  i gt  lt i gt        lt  i gt    query                  AS a CROSS APPLY  XML  nodes  i   AS x i    AS y       WHERE Item IS NOT NULL      GO   Now your stored procedure can just be   CREATE PROCEDURE dbo DoSomethingWithEmployees    List VARCHAR MAX  AS BEGIN   SET NOCOUNT ON     SELECT EmployeeID   Item FROM dbo SplitInts  List         END GO   And in your C  code you just have to pass the list as  1 2 3 12        I find the method of passing through table valued parameters simplifies the maintainability of a solution that uses it and often has increased performance compared to other implementations including XML and string splitting    The inputs are clearly defined  no one has to guess if the delimiter is a comma or a semi-colon  and we do not have dependencies on other processing functions that are not obvious without inspecting the code for the stored procedure    Compared to solutions involving user defined XML schema instead of UDTs  this involves a similar number of steps but in my experience is far simpler code to manage  maintain and read       In many solutions you may only need one or a few of these UDTs  User defined Types  that you re-use for many stored procedures  As with this example  the common requirement is to pass through a list of ID pointers  the function name describes what context those Ids should represent  the type name should be generic

User · Answer

Context is always important  such as the size and complexity of the array  For small to mid-size lists  several of the answers posted here are just fine  though some clarifications should be made    For splitting a delimited list  a SQLCLR-based splitter is the fastest  There are numerous examples around if you want to write your own  or you can just download the free SQL  library of CLR functions  which I wrote  but the String Split function  and many others  are completely free   Splitting XML-based arrays can be fast  but you need to use attribute-based XML  not element-based XML  which is the only type shown in the answers here  though  AaronBertrand s XML example is the best as his code is using the text   XML function  For more info  i e  performance analysis  on using XML to split lists  check out  Using XML to pass lists as parameters in SQL Server  by Phil Factor  Using TVPs is great  assuming you are using at least SQL Server 2008  or newer  as the data is streamed to the proc and shows up pre-parsed and strongly-typed as a table variable  HOWEVER  in most cases  storing all of the data in DataTable means duplicating the data in memory as it is copied from the original collection  Hence using the DataTable method of passing in TVPs does not work well for larger sets of data  i e  does not scale well   XML  unlike simple delimited lists of Ints or Strings  can handle more than one-dimensional arrays  just like TVPs  But also just like the DataTable TVP method  XML does not scale well as it more than doubles the datasize in memory as it needs to additionally account for the overhead of the XML document    With all of that said  IF the data you are using is large or is not very large yet but consistently growing  then the IEnumerable TVP method is the best choice as it streams the data to SQL Server  like the DataTable method   BUT doesn t require any duplication of the collection in memory  unlike any of the other methods   I posted an example of the SQL and C  code in this answer    Pass Dictionary to Stored Procedure T-SQL

User · Answer

There is no support for array in sql server but there are several ways by which you can pass collection to a stored proc     By using datatable  By using XML Try converting your collection in an xml format and then pass it as an input to a stored procedure   The below link may help you   passing collection to a stored procedure

User · Answer

As others have noted above  one way to do this is to convert your array to a string and then split the string inside SQL Server   As of SQL Server 2016  there s a built-in way to split strings called      STRING SPLIT     It returns a set of rows that you can insert into your temp table  or real table    DECLARE  str varchar 200  SET  str    123 456 789 246 22 33 44 55 66  SELECT value FROM STRING SPLIT  str         would yield    value -----   123   456   789   246    22    33    44    55    66   If you want to get fancier   DECLARE  tt TABLE       thenumber int   DECLARE  str varchar 200  SET  str    123 456 789 246 22 33 44 55 66   INSERT INTO  tt SELECT value FROM STRING SPLIT  str        SELECT   FROM  tt ORDER BY thenumber   would give you the same results as above  except the column name is  thenumber    but sorted  You can use the table variable like any other table  so you can easily join it with other tables in the DB if you want   Note that your SQL Server install has to be at compatibility level 130 or higher in order for the STRING SPLIT   function to be recognized  You can check your compatibility level with the following query   SELECT compatibility level FROM sys databases WHERE name    yourdatabasename     Most languages  including C   have a  join  function you can use to create a string from an array   int   myarray    22  33  44   string sqlparam   string Join      myarray     Then you pass sqlparam as your parameter to the stored procedure above

User · Answer

This will help you     Follow the next steps    Open the Query Designer Copy Paste the Following code as it is it will create the Function which convert the String to Int  CREATE FUNCTION dbo SplitInts       List      VARCHAR MAX       Delimiter VARCHAR 255    RETURNS TABLE AS   RETURN   SELECT Item   CONVERT INT  Item  FROM         SELECT Item   x i value     text    1     varchar max            FROM   SELECT  XML    CONVERT XML    lt i gt             REPLACE  List   Delimiter    lt  i gt  lt i gt        lt  i gt    query                  AS a CROSS APPLY  XML  nodes  i   AS x i    AS y       WHERE Item IS NOT NULL      GO  Create the Following stored procedure   CREATE PROCEDURE dbo sp DeleteMultipleId   List VARCHAR MAX   AS  BEGIN       SET NOCOUNT ON        DELETE FROM TableName WHERE Id IN  SELECT Id   Item FROM dbo SplitInts  List           END  GO  Execute this SP Using exec sp DeleteId  1 2 3 12  this is a string of Id s which you want to delete  You convert your array to string in C  and pass it as a Stored Procedure parameter  int   intarray     1  2  3  4  5      string   result   intarray Select x  gt x ToString    ToArray          SqlCommand command   new SqlCommand    command Connection   connection  command CommandText    sp DeleteMultipleId   command CommandType   CommandType StoredProcedure  command Parameters Add   Id  SqlDbType VARCHAR  Value result      This will delete multiple rows  All the best

User · Answer

I ve been searching through all the examples and answers of how to pass any array to sql server without the hassle of creating new Table type till i found this linK  below is how I applied it to my project   --The following code is going to get an Array as Parameter and insert the values of that --array into another table  Create Procedure Proc1     UserId int    just an Id param  s nvarchar max     this is the array your going to pass from C  code to your Sproc  AS      declare  xml xml      set  xml   N  lt root gt  lt r gt     replace  s       lt  r gt  lt r gt        lt  r gt  lt  root gt        Insert into UserRole  UserID RoleID      select          UserId  UserId   t value      varchar max    as  RoleId        from  xml nodes    root r   as a t  END    Hope you enjoy it

User · Answer

Based on my experience  by creating a delimited expression from the employeeIDs  there is a tricky and nice solution for this problem   You should only create an string expression like   123 434 365   in-which 123  434 and 365 are some employeeIDs  By calling the below procedure and passing this expression to it  you can fetch your desired records  Easily you can join the  another table  into this query  This solution is suitable in all versions of SQL server  Also  in comparison with using table variable or temp table  it is very faster and optimized solution   CREATE PROCEDURE dbo DoSomethingOnSomeEmployees   List AS varchar max  AS BEGIN   SELECT EmployeeID    FROM EmployeesTable   -- inner join AnotherTable on       where  List like      cast employeeID as varchar 20        END GO

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

Examples related to c#

Examples related to sql-server

Examples related to tsql

Examples related to stored-procedures