How to pass and Return a Array to stored procedure?

Question

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

Answer 1

There are different options here depending on the scenario. I'm using SQL Server for a lot of the examples below, but much of it is broadly transferable between systems.

For a relatively small array (ideally vector) you can construct a delimited string (tab delimited, comma delimited, whatever), and pass that into your DB and parse - usually manually (DBMS often lack a "split" routine), but it is very easy to obtain a pre-written "split" implementation (for example, as a UDF in SQL Server). Typical usage:

SELECT st.*
FROM dbo.SplitUDF(@myarg) #udf
INNER JOIN SOME_TABLE st ON st.ID = #udf.Value

Xml is another option, especially for complex data; SQL Server 2005 and above has inbuilt xml parsing, but that should not be assumed in general.

Table-valued parameters are another option, but this is SQL Server 2008 only - it might well be what you are looking for, though.

Another option, especially for large data, is to pump the data into the server separately (bulk insert, SQLBulkCopy, "bcp", SSIS whatever) and process the data via SQL once it is there.

To get array/tabular data out, a standard SELECT should be your default option, although you can of course also construct xml or delimited character data. The latter can be accomplished via a quirk of SQL:

DECLARE @foo varchar(max)
SET @foo = ''
SELECT @foo = @foo + st.SomeColumn + '|' -- pipe-delimited, note trailing |
FROM SOME_TABLE st