How to-Combine 3 Storedprocedures O/P to get one output report without UNION clause

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » How to-Combine 3 Storedprocedures O/P to...

How to-Combine 3 Storedprocedures O/P to get one output report without UNION clause

Rate Topic

Display Mode

Topic Options

Author

Message

dhananjay.nagarkar

Posted Tuesday, March 05, 2013 6:25 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:47 AM
Points: 39, Visits: 74

Hi Friends,

I have very complex functionality-

I have developed 3 Stored Procedures-
1) Jobposting,
2) WorkOrder and
3) Worker

now the final step I want to do is-

Combine the 3 Stored Procedures and get one single output the problem with UNION clause is that each of the 3 STored procedures have 2 sections of code - the 1st section has fields in different order than the 2nd second section for each stored procedure, and there are 300 fields so I wanted to know without the manual effort maintaing the same fields order in both sections of the code for each pass, instead is there anyother way this can be done using TEMP table in the start if yes , would be much obliged if I could get a sample code to get my above functionality accomplished please.

Thanks
Dhananjay

Post #1427109

buddy__a

Posted Wednesday, March 06, 2013 9:59 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 1:33 PM
Points: 304, Visits: 167

What's the problem with using UNION?

If all three procs and each section output the same fields then why not just order them the same? It's not good to develop apps the rely on specific field ordering. Using INSERT INTO ... EXEC would allow you to save the output of your stored procs into a table then report from that table.

My question now is, are you getting all of your data from the same section of each of your procs or is there an instance where you'd be getting some data from section 1 in one or two of the procs and then from section 2 in the others? If so, you'd have to use some dynamic sql to store data in a temp table that matches the output you're getting then add that data to the main reporting table afterwards. Maybe you can tell us more about the nature of your procs and processes that use them so that another solution may be offered.

Post #1427510

Chris Harshman

Posted Wednesday, March 06, 2013 10:00 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 12:03 PM
Points: 1,564, Visits: 1,718

If you create a temp table before the 3 stored proc calls, you should be able to do an INSERT INTO (column_list) EXEC execute_statement on each of the 3 stored procs with different columns or different order of columns in your column_list. For each one just make sure the column_list matches the count and type of columns in the resultset of each stored proc.

http://msdn.microsoft.com/en-us/library/ms174335(v=sql.105).aspx

Post #1427511

buddy__a

Posted Wednesday, March 06, 2013 10:21 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 1:33 PM
Points: 304, Visits: 167

Here's a little follow up on the INSERT EXEC statement. Say you have a table defined as such

create table #ReportData (a int, b int, c varchar 100)

Then your proc outputs data (the same fields) in this order

select c, a , b from datasource

or in the second section it comes out like this:

select b, c, a from datasource

You can then execute this:

insert #ReportData (c, a, b)
EXEC (myProc1)

insert #ReportData (b, c, a)
EXEC (myProc2 @outputsection = 2)

insert #ReportData (c, a, b)
EXEC (myProc3)

Then you can report on the data in #ReportData. Of course you may have to use some logic and/or dynamic sql to properly handle which table ordering you're going to get from each proc.

Post #1427519

dhananjay.nagarkar

Posted Wednesday, March 06, 2013 2:34 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:47 AM
Points: 39, Visits: 74

Hi friends,

the temp table idea helped me-

I used the logic as below- it helped me thanks a lot!!!

//

create table #tmp_Set_Flags- temp table created
insert into #tmp_Set_Flags
select JP RECORDS
FROM [WorkForce_JobPosting] –
Select
into #tmp_SLII_Report
from #tmp_Set_Flags
select * from #tmp_SLII_Report
Delete * from #tmp_Set_Flags
insert into #tmp_Set_Flags
Select WO RECORDS
FROM [WorkForce_WorkOrder] –
Select
into #tmp_SLII_Report
from #tmp_Set_Flags
select * from #tmp_SLII_Report
Delete * from #tmp_Set_Flags
insert into #tmp_Set_Flags
Select WOR (Revision) RECORDS
FROM [WorkForce_WorkOrder] –
Select
into #tmp_SLII_Report
from #tmp_Set_Flags
select * from #tmp_SLII_Report—final report display

//

Kind Regards
Dhananjay

Post #1427638

« Prev Topic | Next Topic »

Permissions