SQL Server 2005 - Passing 2-Dimensional Array to Stored Procedure

Question

Hi,

I am working on SQL Server 2005.

Here I need to pass an array (2 dimensional, E.g: Emp No, Emp Name ) having multiple records from my ASP.NET application to a Stored Procedure.

Would you please let me know if there is a way to do it?

Many Thanks,

Regards.

Anusha.

Answer 1

This sounds roughly equivalent to sending a table with two columns:

table Employees
.No
.Name

In SQL 2008 you can use a table type parameter to pass this sort of data, but I don't believe this is available in Sql 05.

If you're using .NET, you can use the SqlBulkCopy class to send a batch of records.

If none of these work, you may have to execute a single stored procedure per row (per employee in this case). This is assuming that this data needs to wind up in a table, as opposed to attempting to parse or reformat the data on the SQL server. If the latter is the case, you should really perform such operations on the client instead of the database server.

Answer 2

You could create a temporary table and have proc 1 populate it and have proc 2 select from it if you are using 2005. In 2008 you can pass table valued parameters around. Some people may say you could pass in XML too.

Answer 3

In 2005 I don't think there is a way of doing this elegantly. In the past I have done this by passing in lists of values as text strings e.g. "1,2,3". We have a user defined function that splits lists and returns a table. Just need to be careful that the length of the list doesn't get longer than your parameter length.

Answer 4

Pass the array in xml format, like:

<array>
   <row>
      <cell>1</cell>
      <cell>2</cell>
      <cell>3</cell>
   </row>
   <row>
      <cell>4</cell>
      <cell>5</cell>
      <cell>6</cell>
   </row>
</array>

through an argument of type xml. SQL 2005 has xml manipulation operators that you can use inside the stored procedure to parse the array.

Answer 5

There are 6 different approaches outlined here

I personally like the XML approach outlined by Nestor as this can handle an array of any dimensions, although others may prefer fn_split , passing a delimited string and unpacking it into a table variable to join to for simple cases.

All is solved in SQL2008 - where a set based language finally allows a table variable as an input parameter!