Combine 2 unrelated queries into a single SP

Question

Post reply

Combine 2 unrelated queries into a single SP

var05

Hall of Fame

Points: 3011
More actions
December 17, 2012 at 8:01 am

#267623

Hi All,
Is there any way to combine multiple SQL stored procedures into a single SP ,so that the single SP can be used to generate a SSRS report
Say
FROM SP A- Resutls1
FROM SP B- Resutls2
In SSRS report I got to combine Results1+Results2
or any other ways to combine 2 unrelated queries so that it can be added on SSRS end.
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

create two different datasets. 🙂

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

var05 Hall of Fame Points: 3011 More actions · Answer 2

Ya can create two datasets...

But summing up the value from Dataset1+Dataset2 doesnt work in a matrix report

Say I have a rpt like

COl Week1 Week2 Week3

A Sp1+Sp2 Sp1+SP2 SP!+SP2------------------ ie results from two diff datasets/results sets...

It shows correct value only for Week1, n not for the other weeks when I combine 2 diff datasets.

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Please will you post an example of the results of SP A and SP B?

Thanks

John

var05 Hall of Fame Points: 3011 More actions · Answer 4

Yah,

Say SP A's results will be in this format as below:

EmpNoEmpName TotalAWeek

1A 2 20/08/2012

1B 3 20/08/2012

2C 2 20/08/2012

9D 2 20/08/2012

And SP B

AREA TOTALB

20/08/2012XYZ514

bth SPA and SPB are from diff tables that has no relation. Say only week would be d column which is same in both procs. Apart from tht nothing has relation

But as per req....

I wanted the SSRS report As below

WEEK 20/08/2012

CalcColA 514++3+2+2

i.e for the same week combine and add values from 2 result sets ( TotalA+TotalB)

Thanks!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Yes, you can get that result with a single stored procedure. If you wanted to use the output of A and B, you'd have to fiddle about with temp tables, so if you have the option, I think it's best to write a new stored procedure to get the single result set.

John

var05 Hall of Fame Points: 3011 More actions · Answer 6

Thank You

You mean to say from SPA- get the results into a temp table

SPb- get the results into another temp table

and combine their results?? something like this?

Thanks!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

I'm saying that's one way of doing it, yes, but not the way I recommend. If you have access to the definitions of the existing procs and permission to create a new one, I would write a stored procedure that joins the two result sets without having to use temp tables.

John

var05 Hall of Fame Points: 3011 More actions · Answer 8

Im just thinking wat other ways wld be possible to join the result set of 2 sp's apart from using Temp table....

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

Well, you could do the join in Integration Services. Or maybe Reporting Services has similar functionality as well - I've never tried it. But just because you can do something, it doesn't mean you should - do it the way I first suggested if you can.

John

var05 Hall of Fame Points: 3011 More actions · Answer 10

I ve tried out in SSRS.....It causes prob...alright wil try wit temp tables...thank u

sgmunson SSC Guru Points: 110640 More actions · Answer 11

John Mitchell-245523 (12/17/2012)
Well, you could do the join in Integration Services. Or maybe Reporting Services has similar functionality as well - I've never tried it. But just because you can do something, it doesn't mean you should - do it the way I first suggested if you can.
John

And, the original poster might also need to avoid having to change their SP, if the original SP1 and/or SP2 get changed. Keeping them in sync could be a problem, so outputting the results from each into a temp table and doing some form of UNION query might well be EXACTLY the right thing, under the circumstances. Hard to know, however, without more detail from the OP.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)