Eurodata Computing - Technical Tips

Technical Tips


	Home About us Clients Technology SQL Server C# ASP.net XML & XSL Software VB AutoCoder VB AutoNumber RegServer Tech. Tips Downloads Links Our sites Useful Contact us

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:

   au_id
   -----------
   427-17-2319
   846-92-7186
   899-46-2035
   998-72-3567

   (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:

   CREATE TABLE #temp
   (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.