Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to get output from stored procedures Expand / Collapse
Author
Message
Posted Monday, August 3, 2009 2:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 7:56 PM
Points: 74, Visits: 264
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.
Post #764373
Posted Monday, August 3, 2009 3:07 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:16 AM
Points: 1,035, Visits: 410
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



*****************/
Post #764409
Posted Monday, August 3, 2009 4:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 30, 2014 5:24 PM
Points: 7,139, Visits: 15,191
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?
Post #764452
Posted Monday, August 3, 2009 7:32 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, Visits: 6,098
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? -- Stephen Stills
Post #764510
Posted Monday, August 3, 2009 8:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 7:56 PM
Points: 74, Visits: 264
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.
Post #764544
Posted Tuesday, August 4, 2009 7:24 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, Visits: 6,098
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? -- Stephen Stills
Post #764787
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse