September 18, 2003 at 3:02 pm
How can I use the resultset returned by a stored proc in another stored proc?
My first stored proc allows for input parameters and summarizes information using a "Group By" clause.
I want to be able to use the results from this stored proc in my "From" clause as a table in my second stored proc so that I can use the summary fields in calculations like colA / colB.
I could accomplish this task using a derived table in the first stored proc but it is already built out and it would be much easier to just reference the first stored proc like it as a table.
Any ideas?
Thanks!!!
September 18, 2003 at 3:45 pm
If you are using SQL2K and the resultset is less than ~10K records or so, you can use a TABLE datatype to store the return. If not on SQL2K or you have a very large dataset returned, use temporary tables. Look in BOL for explanation of INSERT INTO Table EXEC StoredProcedure syntax.
--
HTH,
Jay
September 18, 2003 at 3:52 pm
I'm using SQL Server 7. Is there any other way to do this than using a temp table and without modifying my original stored proc 1 to use a derived table?
What I'm looking for is something like...
SELECT * FROM (exec usp_stored_proc_1)
September 18, 2003 at 3:59 pm
No, the functionality you are describing is unique to SQL2K in its User Defined Functions and TABLE datatype.
--
You will have to use temporary tables, AFAIK.
September 18, 2003 at 4:10 pm
Do you know what the differences are in performance for using the temp table method vs. using a derived table in the stored proc?
September 19, 2003 at 6:14 am
In my experience, the performance results have depended on the size of the dataset being manipulated. SQL Server uses slightly different methods of creating worktables and temporary tables. Your best bet is to try each approach and test, test, test. Remember, however, that, depending on the size of the dataset, it may be useful to explore new or alternate indexes for the temporary table and the table being derived from. Remember that SQL must build a worktable in tempdb (or raw memory) in order to aggregate for a group by clause. This step can often be avoided with covering indexes. If you post some code, I can help clarify some of these points if you'd prefer.
--
Jay
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply