How can I return a list of values from a stored procedure?

Question

Well I am calling a stored procedure from another stored procedure and I need it to return something like an array.How can I do it?

Answer 1

You can do this with a temporary table or actual table. Note that (unfortuantely) it doesn't work with table variables.

Set up your table:

CREATE TABLE #Foo (Bar NVARCHAR(50), BAZ INT)

Then your stored procedure:

CREATE PROCEDURE [GetFoo] AS
BEGIN
    SELECT [BarCol], [BazCol] FROM [BarBazSource]
END

Then use the EXEC T-SQL command:

INSERT #Foo EXEC [GetFoo]

Your #Foo temporary table now contains the output of the [GetFoo] stored procedure.

For more information there's an MSDN article here: "Getting Creative with EXEC and EXEC()"

Answer 2

May I suggest that you take a look at Sommarskogs homepage on Arrays and Lists in SQL Server?

His homepage is full of information, so set aside a few hours to read everything. But start of with this link. It is only about 60 screens full ;-)
IMHO it covers everything on passing info between stored procedures. Highly recommended. And you really only need to read the first few pages.