I've always found working with stored procedures that return tabular data somewhat cumbersome within the context of another stored procedure. In other words, it's not the most intuitive of tasks to call a stored procedure that returns a rowset and work with its data from within a stored procedure.
There are several approaches that one might take to accomplish this. Creating a temporary table or a CURSOR come to mind immediately as two valid approaches. In fact, in SQL Server 2000, I found a temp table to be the most reliable way to work with data in this manner.
As a for instance, there is a stored procedure in SQL Server called sp_server_info. This procedure, when executed without parameters, will return a three-column resultset consisting of an ID, an attribute name, and an attribute value identifying various attributes of the SQL Server itself. You can also call the stored procedure specifying the ID and it will return the row identified by the ID.
Furthermore, SQL Server 2000 introduced the concept of TABLE-type variables. However, they were largely useless in this context because you couldn't INSERT INTO a table variable the resultset from a stored procedure.
This changes in SQL Server 2005, however. Let me illustrate this with a very simple, contrived example.
Suppose that I wanted to call the sp_server_info from a stored procedure, work with the data, and return it to the caller.
CREATE PROCEDURE dbo.GetSprocVersion AS
-- Create a table variable that matches the resultset of the stored procedure
DECLARE @tmp TABLE (
attribute_id int, attribute_name varchar(60), attribute_value varchar(255)
)
-- Populate the table variable by invoking the stored procedure
INSERT INTO @tmp EXEC sp_server_info
-- Return the appropriate result
SELECT attribute_value FROM @tmp WHERE attribute_id = 500
GO
What I like about this example, if you look closely, is that you can call INSERT INTO..EXEC on a TABLE variable :) - very cool! Now, I can make this code slightly more exciting by doing the following:
CREATE PROCEDURE dbo.GetSprocVersion AS
-- Create a table variable that matches the resultset of the stored procedure
DECLARE @tmp TABLE (
attribute_id int, attribute_name varchar(60), attribute_value varchar(255)
)
-- Populate the table variable by invoking the stored procedure
INSERT INTO @tmp EXEC sp_server_info 500
-- Return the appropriate result
SELECT attribute_value FROM @tmp
GO
In this particular example I invoke the stored procedure passing a parameter.
Ok, this isn't rocket science, but as you can see, it makes it very easy to work with set data using a TABLE variable. I envision this may be more useful were you to call a sproc that returned a larger set (say a set of customers) and then process that result to return either a finer resultset, massage the data, or otherwise.