Combining outputs
-
I want to combine the outputs of several stored procedures into one output. Is this possible or will I have to change the selects into inserts and put the data into a table?
Robert T Turner -
You cannot union the results of many stored procedures.
you can insert results from each stored procedure into a temp table, and then select from that.
Or create a new stored procedure with the queries from each procedure unioned in 1 procedure.
-
Well it can be done... but I'd strongly suggest going to the insert into temp table solution.
-
I am curious ,, can you post sample code for your solutions?
Thanks,
Erik....
Dam again!
-
No, you still have so much stuff to learn... like using a function to do this task
. -
The best way to utilize a recordsets output from a stored procedure is through a function. I recommend that instead of having SP#1 call SP#2 for data, that SP#2 be converted to a function; then call the function from SP#1.
Many features of a stored procedure are available through a function; for example:
CREATE FUNCTION dbo.MyRst
RETURNS @tblResult
TABLE ( iCompanyID INT NOT NULL
, iValue INT NOT NULL )
AS
BEGIN
INSERT INTO @tblResult ( iCompanyID, iValue)
SELECT iCompanyID, iValue
FROM tblX
RETURN
END
Returns a table which can be implemented in a SP simple by:
SELECT * FROM dbo.MyRst
However there are cases when a function cannot be used as a replacement for a stored procedure. SQL functions don't support non-deterministic embedded functions such newid() or getdate(). However in some cases you can pass in an input parameter for that non-deterministic value.
There are some t-sql capabilities that are not possible in functions and limited to stored procedures which I won't go into... so if you're at this point and must use an embedded SP resultset inside another SP there is a couple ways. I'll just cover the simplest and most straightfoward for now.
First create a temp table, #tmpTbl, (note, this cannot be a @variable temp table) with the exact same columns as the SP recordset output. Second, insert into it via: INSERT INTO #tmpTbl EXEC mySP
Note that mySp must be return a single recordset.
Hopefully the function is ideal for you; otherwise I hope the SP insert works well; best of luck with your application.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply