April 28, 2004 at 2:41 pm
Hi all,
i have actually two queries
1. I am stuck in a problem, actually i want to get all the rows from a SP into a temporary table. the sp returns different type of resultset depending on arguments. so i cant create a temp table as resultset depends on arguments.
i-e insert into #tmpTable sp_Magic arg1, arg2
2. Is it possible to select all rows from exec sp ( select * From exec sp_magic)
May 3, 2004 at 8:00 am
This was removed by the editor as SPAM
May 3, 2004 at 8:46 am
Well,
I did not understand the questions properly, however from what I understand, if you want to insert the output of a SP call to a temporary table, the first thing is you need to create a temp table with max number of columns that may be resulting from the SP output and default each column to a appropriate value based on the datatype, one problem that I see here is even the datatypes that are returned from the SP may not be consistent in order from what you mentioned in your post.
The answer for second question is No, I mean there is nothing like select * from EXEC SP_MAGIC
EXEC SP_MAGIC would itself return the whole resultset.
Thanks
Prasad Bhogadi
www.inforaise.com
May 3, 2004 at 3:30 pm
You can create the temp table outside of the procedure with the columns that are consistent between the two(or more) versions of the table. Then in the sp_magic procedure, you can alter the temp table to add the additional columns needed.
IE.
CREATE TABLE #Temp (Report_id INT)
EXEC SP_Magic
Then inside of SP_Magic
ALTER TABLE #Temp ADD Login_id INT, ExecuteStart DATETIME, etc
Brian
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply