By Sergey

2012-06-19 13:47:01 8 Comments

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#.


@Aaron Bertrand 2012-06-19 16:35:39

SQL Server 2008 (or newer)

First, in your database, create the following two objects:


CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List AS dbo.IDList READONLY


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)

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:

   @List      VARCHAR(MAX),
   @Delimiter VARCHAR(255)
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
        FROM ( SELECT [XML] = CONVERT(XML, '<i>'
        + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y

Now your stored procedure can just be:

CREATE PROCEDURE dbo.DoSomethingWithEmployees

  SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ','); 

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.

@Drammy 2013-05-09 22:35:59

I like the table parameter idea - never thought of that - cheers. For what it's worth the delimiter needs passing into the SplitInts() function call.

@bdwain 2013-05-23 18:01:45

How can I use the table parameter if only have access to a comma separated string

@Aaron Bertrand 2013-05-23 18:09:20

@bdwain would defeat the purpose - you'd have to use a split function to break it into rows to put into the TVP. Break it out in your application code.

@RobHurd 2014-07-15 00:57:42

This is great, thanks. How do you pass the table parameters as a string rather than constructing it in C#? i.e. would it be like @List = [(1, 'Jane')(2, 'Smith')(3, 'Chris')]

@Aaron Bertrand 2014-07-15 12:52:56

@RobHurd That would defeat the purpose - the whole point is to stop passing a string.

@JaKXz 2014-12-10 21:01:41

I'm having an issue with SQL Server 2012: I'm using the custom list type in a SP like you are, but using it with the IN clause like so: SELECT [columnA] FROM [MyTable] WHERE [Id] IN (@ListOfIds) -- when altering my procedure that's doing something with the list it says I must Declare the scalar variable @ListOfIds, but it's in the parameters section of my SP and putting DECLARE in front of it gives me lots of syntax errors... any ideas @AaronBertrand ?

@Aaron Bertrand 2014-12-10 22:57:01

@JaKXz not without seeing the code, no. Can you post it somewhere? What is the compatibility level of the database?

@JaKXz 2014-12-10 23:36:55

@AaronBertrand thanks for the response, I actually just figured it out. I have to use a sub select in the brackets: SELECT [colA] FROM [MyTable] WHERE [Id] IN (SELECT [Id] FROM @ListOfIds).

@th1rdey3 2015-01-22 10:50:16

@AaronBertrand, How can I make the @list parameter optional. i.e. setting a default parameter value so that I don't have to pass it a value if I don't need to. @List AS dbo.EmployeeList READONLY = null doesn't work.

@Aaron Bertrand 2015-04-01 14:30:18

@th1rdey3 They are implicitly optional.

@hakan 2017-06-09 08:38:04

FYI: I had to add p.TypeName = "dbo.MyType";

@O. R. Mapper 2017-09-27 14:02:13

"I think there are more straightforward ways than XML" - maybe I'm missing some context, but where does the OP claim they want to use XML?

@Aaron Bertrand 2017-09-27 14:34:45

@O.R.Mapper The OP doesn't ask for a specific method, but other answers suggest it.

@Solomon Rutzky 2014-09-13 22:00:16

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

@Charan Ghate 2014-12-20 11:46:05

This will help you. :) Follow the next steps,

  1. Open the Query Designer
  2. 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)
          ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
            FROM ( SELECT [XML] = CONVERT(XML, '<i>'
            + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
              ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
          WHERE Item IS NOT NULL
  3. Create the Following stored procedure

     CREATE PROCEDURE dbo.sp_DeleteMultipleId
     @List VARCHAR(MAX)
          SET NOCOUNT ON;
          DELETE FROM TableName WHERE Id IN( SELECT Id = Item FROM dbo.SplitInts(@List, ',')); 
  4. Execute this SP Using exec sp_DeleteId '1,2,3,12' this is a string of Id's which you want to delete,

  5. 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=>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

@Saboor Awan 2016-06-24 05:59:24

i have used this comma separate parsing function, it would work for small data-set, if u check its execution plan, it will cause issue on large data-set and where u have to multiple csv list in stored procedure

@Hamed Nazaktabar 2014-12-02 15:16:24

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)
  SELECT EmployeeID 
  FROM EmployeesTable
  -- inner join AnotherTable on ...
  where @List like '%;'+cast(employeeID as varchar(20))+';%'

@MDV2000 2016-04-22 20:07:47

Nice! I really like this approach where I am filtering on int keys! +1

@Hamed Nazaktabar 2016-05-25 05:05:43

@MDV2000 thanks :) On string keys, this also have good performance because of not casting int columns to varchar ...

@Adam 2014-07-17 16:34:16

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


    declare @xml xml

    set @xml = N'<root><r>' + replace(@s,',','</r><r>') + '</r></root>'

    Insert into UserRole (UserID,RoleID)
       @UserId [UserId], t.value('.','varchar(max)') as [RoleId]

    from @xml.nodes('//root/r') as a(t)

Hope you enjoy it

@zaitsman 2014-08-28 06:29:40

no idea why this is not upvoted this is the CLEANEST solution of all imo...

@Solomon Rutzky 2014-09-13 21:23:10

@zaitsman: CLEANEST does not mean best or most appropriate. One often gives up flexibility and/or "appropriate" complexity and/or performance to get "clean" code. This answer here is "ok" but only for small data sets. If the incoming array @s is CSV then it would be faster to simply split that (i.e. INSERT INTO...SELECT FROM SplitFunction). Converting to XML is slower than CLR, and attribute-based XML is much faster anyway. And this is a simple list yet passing in XML or TVP can also handle complex arrays. Not sure what is gained by avoiding a simple, one-time CREATE TYPE ... AS TABLE.

@eselk 2013-12-05 04:39:02

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:

    @excludeIngredientsString varchar(MAX) = ''
    /* 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)

@Solomon Rutzky 2014-09-13 22:06:44

Generally speaking, it is best to not JOIN to a Table Variable, but instead to a Temp Table. Table Variables, by default, only appear to have one row, though there is a trick or two around that (check out @AaronBertrand's excellent and detailed article:…).

@Fedor Hajdu 2012-06-19 13:49:37

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)
    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')
    AS N'<xs:schema xmlns:xs="">
    <xs:element name="arrayOfUlong">
                <xs:element maxOccurs="unbounded"
                            type="xs:unsignedLong" />

@ganders 2012-06-19 17:32:15

I thought it was a bad idea to use table variables when there are many rows? Isn't it better performance to use a temp (#table) table instead?

@abatishchev 2014-12-23 23:11:33

@ganders: I'd say vice versa.

@praveen 2012-06-19 13:54:52

There is no support for array in sql server but there are several ways by which you can pass collection to a stored proc .

  1. By using datatable
  2. 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

@Levi W 2012-06-19 13:53:17

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:


// Assumes connection is an open SqlConnection object.
using (connection)
// Create a DataTable with the modified rows.
DataTable addedCategories =

// 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.

Related Questions

Sponsored Content

26 Answered Questions

22 Answered Questions

[SOLVED] Search text in stored procedure in SQL Server

26 Answered Questions

39 Answered Questions

[SOLVED] Parameterize an SQL IN clause

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

18 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

11 Answered Questions

[SOLVED] SQL Server: How to Join to first row

20 Answered Questions

[SOLVED] Get size of all tables in database

  • 2011-10-25 16:14:34
  • Eric
  • 1109991 View
  • 1082 Score
  • 20 Answer
  • Tags:   sql-server tsql

37 Answered Questions

41 Answered Questions

[SOLVED] Table Naming Dilemma: Singular vs. Plural Names

Sponsored Content