August 3, 2009 at 2:13 pm
I need to get the output from the stored procedures (50 of them),
and insert into a temp table at each execution of a stored procedure.
Is there a way for me to do this WITHOUT having to pre-define the
temp table (because the columns are all different for each stored procedure
output, otherwise I would have to create 50 temp tables, which I prefer
not having to do it like that).
I am not allowed to change the stored procedures. The stored procedures do not have any parameters, so I cannot use OUTPUT.
if I have to create that many temp tables, then I guess I would have to,
but I just wanted to make sure there is no other better option.
any help/advice would be greatly appreciated.
August 3, 2009 at 3:07 pm
Sorry to tell you this, but I think you are in for creating a bunch of temp tables. You can't execute a stored procedure from within a function or a select statement so that rules out being able to use the SELECT...INTO syntax and other than that the table that you are inserting into must already exist.
However, if the stored procs don't have any parameters, why don't you define views and use those? A procedure with no parameters and that simply returns a result set is pretty much a view...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
August 3, 2009 at 4:01 pm
Well - temp tables are certainly one way... but they're not the ONLY way.
depending on what you want to do with them, you can set up a "self-linked server" and then use them a bit like table-valued functions and access their output as a table variable.
As in - something like
Select *
from openquery(MyLocalServer, 'exec mydbo.dbo.myStoredProc @Param=1')
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 3, 2009 at 7:32 pm
Are all the columns in common between the 50 stored procedures? Even if you defined a temp table with enough columns to handle any of the stored procedures, you would still have to define which columns are going to be populated by the output of the stored procedures. For example
create table #temp (many many columns)
insert into #temp (colA,colB,colC)
exec stproc1
insert into #temp (colA,colC,colD,colE)
exec stproc2
insert into #temp (colA,colB,col,C,colD,colE,colF,colG...)
exec stproc99
There just isn't an "automatic" way to avoid all this with stored procedures.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 3, 2009 at 8:46 pm
Thank you all for responding.
Yes, DCPeterson, creating that many temp tables is what I was afraid of.
I will look into Matt's suggestion, but I'm not sure it would be any easier.
And Quickly Confused is right, there is no auto way to do this
since none of the stored procedure output have the common columns.
This is what I needed to hear before I proceed.
thanks a bunch.
August 4, 2009 at 7:24 am
Good luck, blue. It's really not difficult, just tedious and time consuming.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply