How to pass an array into a SQL Server stored procedure

How to pass an array into a SQL Server stored procedure?

For example, I have list of employees. I want to use this list as a table and join it with another table. But list of employees should be passed as parameter from C#.

How to pass Table-Valued Parameters (Array-like Parameter) to Stored Procedure in Microsoft SQL Server 2008 R2 using JDBC?

How to pass Table-Valued Parameters (Array-like Parameter) to Stored Procedure in Microsoft SQL Server 2008 R2 using Microsoft SQL Server 2008 R2 JDBC Driver ? Is it possible with jTDS?

Passing array to a SQL Server Stored Procedure

How can I pass an array variable to a SQL Server stored procedure using C# and insert array values into a whole row? Thanks in advance. SQL Server table: ID | Product | Description ——————-

How to pass a 3D array to a SQL Server stored procedure

I have a 3D string array in C# and I need to send it to a SQL server stored procedure as a parameter so that the procedure performs a select query using WHERE condition from each array row (3 conditio

Delphi – pass table valued parameter to SQL Server stored procedure

I need to pass the parameter as table value for a stored procedure in SQL Server. How to handle this in Delphi?

Pass Japanese Characters to a SQL Server stored procedure

I’m trying to pass Japanese characters to a stored procedure in SQL Server. The characters get converted to ???? during execution and hence I get incoreect results. How do I pass them such characters?

SQL Server – stored procedure

I am trying to create a stored procedure in SQL Server 2008. According to the parser, the syntax is OK. However, when I try to execute the stored procedure and pass actual values, the following error

How to pass XML from C# to a stored procedure in SQL Server 2008?

I want to pass xml document to sql server stored procedure such as this: CREATE PROCEDURE BookDetails_Insert (@xml xml) I want compare some field data with other table data and if it is matching that

How to pass an array of floats to SQL Server stored procedure?

I need to write an array of 36 floats to a SQL Server 2005 database. Is there a best-practices answer which explains how to do this? I can’t write it as a string because we may need to query individu

How to pass string array of Columns as parameter to SQL Server Stored procedure

I have a comma delimited string with column names i.e. Column1, Column2, Column3 which will be passed as a parameter to the following stored procedure. My Stored Procedure is as follows: @ColNames

How to pass and Return a Array to stored procedure?

I would like to know how to pass an array to a stored procedure which in turn will return an array from c# .net?

Answers

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'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="arrayOfUlong">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded"
                            name="u"
                            type="xs:unsignedLong" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>';
END
GO

You can also just pass a table-valued parameter (it will be READONLY by restriction).

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();
}

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

Since you have a List already, I think there are more straightforward ways than XML.

SQL Server 2008 (or newer)

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

CREATE TYPE dbo.EmployeeList
AS TABLE
(
  EmployeeID INT
);
GO

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List AS dbo.EmployeeList READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT EmployeeID FROM @List; 
END
GO

Now in your C# code:

DataTable tvp = new DataTable();
// define / populate DataTable from your List here

using (conn)
{
    SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
    tvparam.SqlDbType = SqlDbType.Structured;
    // 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, '<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
  );
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 recommend you compare the maintainability and performance of these options against the method you selected.

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)

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'<root><r>' + replace(@s,',','</r><r>') + '</r></root>'

    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

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<string,int> to Stored Procedure T-SQL.

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

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) ) RETURNS TABLE AS RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( SELECT Item = x.i.value(‘(./text())[1]’, ‘varchar(max)’) FROM ( SELECT [XML] = CONVERT(XML, ‘‘ + REPLACE(@List, @Delimiter, ‘‘) + ‘‘).query(‘.’) ) AS a CROSS APPLY [XML].nodes(‘i’) AS x(i) ) AS y WHERE Item IS NOT NULL ); GO

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