Eurodata Computing Logo Technical Tips



Tip : 1
Technology : SQL Server
Title : Using the resultset returned by one stored procedure in another.
or Storing the results of a Stored Procedure in a Temporary Table
Stored procedures are often used to return a resultset. Normally this resultset is used immediately, perhaps by displaying it to the user in Query Analyzer or by passing it back to a calling application. However what if we want to use the resultset in another stored procedure ? Unfortunately we can’t just treat the stored procedure as a sort of table or view and perform further joins on it, at least not directly. However what we can do is to load the resultset into a temporary table and use this as the basis for further processing.

To demonstrate this lets take the ‘byroyalty’ stored procedure in the pubs database. We can run the stored procedure in query analyzer with a parameter of 50 by typing in the following, and clicking the ‘Execute Query’ button :

   EXEC byroyalty 50

We get the following results:


   (4 row(s) affected)

To use this in another stored procedure we need to create a temporary table and dump the results into that, as follows:

   (au_id varchar(11))

   INSERT #temp EXEC byroyalty 50

The resultset is then available in the temporary table - this can be verified by running a select from the temporary table:

   SELECT * from #temp

The key is to make sure that the temporary table has the correct structure and data types to accept the data from the stored procedure. Unfortunately it’s not possible to avoid the need to explicitly define the temporary table by doing a SELECT INTO. However if you are using SQL Server 2000 and are able to make use of User-Defined Functions then this problem can be resolved by using UDFs and avoiding stored procedures and temporary tables altogether.

Need further technical assistance ? Click here for more technical information.

Tech Tip Index

This article is provided 'as is' and no responsibility can be accepted for any errors. No part of this article may be used or reproduced in any fashion without the prior consent of Eurodata Computing Ltd. For syndication enquiries please contact us.
Site designed and maintained by Eurodata Computing
© Eurodata Computing 2000-13    Last updated : April 2013
Valid XHTML 1.0 Transitional Valid CSS!