March 5, 2013 at 6:25 pm
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
March 6, 2013 at 9:59 am
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.
March 6, 2013 at 10:00 am
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
March 6, 2013 at 10:21 am
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.
March 6, 2013 at 2:34 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply