February 8, 2007 at 2:09 pm
Hello, I have a report which uses 4 stored procedures. The reason I have 4 is because I am doing Sum and Count functions, and then grouping - and since I have several joins, multiple queries is the only way I can find to do this without returning individual rows (otherwise my joins duplicate the higher level data).
Anyway, I would love for my 4 stored procedures to be able to return 1 row; and I'm wondering if you have any thoughts on the best way to do this. I have briefly looked into openquery, temp tables, transitioning from SPs to UDFs, etc., and would really appreciate any advice.
Thanks!
February 8, 2007 at 2:13 pm
i'm assuming the procedures return the same number of rows? or it's the same proc with different paramters, or that they return a single value?
Hopefully this will get you started down the right path:
Create table #results (MyCount int,MyBucks money)
insert into #results(MyCount,MyBucks) exec proc1(param1)
insert into #results(MyCount,MyBucks) exec proc2(param1)
insert into #results(MyCount,MyBucks) exec proc3(param1)
select sum(MyCount),sum(MyBucks) from #results
note that this is functioanlly equivilent to this, so it's not obvious, but you could do it all in a single proc:
select sum(Virtual_Table.MyCount),sum(Virtual_Table.MyBucks) from
(
SELECT ...--all the complex stuff that occurs in Proc1
UNION SELECT ...--all the complex stuff that occurs in Proc2
UNION SELECT ...--all the complex stuff that occurs in Proc2 ) Virtual_Table
Lowell
February 8, 2007 at 2:42 pm
Thanks Lowell,
Well a union won't quite work for me - let me explain a little better what I'm trying to do.
I'm grouping my data by Office; so I want a row for each office with a column for each piece of data (which due to summing and grouping is distinct).
My stored procedures look something like this:
SP1
Office Code | Office Name (Returns all offices - as Parent).
Sp2
Office Code | Order Count | Order Total
Sp3
Office Code | Item Count | Item Cost | Item Revenue
What I want to end up (in a perfect world) is
Office Code | Office Name | Order Count | Order Total | Item Count | Item Cost | Item Revenue
as one row.
I'm doing this now in Crystal Reports - but it takes like 10 times longer than it should and I know I could speed it up a whole bunch by doing the legwork in SQL.
Thanks again!
February 9, 2007 at 11:00 am
i think something like this, which does it all in one fell swoop, is what you are after:
there are three virtual tables, x1,x2,x3, which are really the big select statements you are doing in your procedures. without the tables and all, this is just a model, but this should be what you are after. you'd probably create this as a view and query the view from crystal.
SELECT
x1.[Office Code],
x1.[Office Name],
x2.[Order Count] ,
x2.[Order Total],
x3.[Item Count],
x3.[Item Cost],
x3.[Item Revenue]
FROM
(
SELECT
SOMETABLE.[Office Code],
SOMETABLE.[Office Name]
FROM SOMETABLE
GROUP BY
SOMETABLE.[Office Code],
SOMETABLE.[Office Name]
) x1
LEFT outer join
(
SELECT
SOMETABLE2.[Office Code],
SOMETABLE2.[Order Count] ,
SOMETABLE2.[Order Total]
FROM SOMETABLE2
GROUP BY
SOMETABLE2.[Office Code],
SOMETABLE2.[Order Count] ,
SOMETABLE2.[Order Total]
) x2
on x1.[Office Code] = x2.[Office Code]
LEFT outer join
(
SELECT
SOMETABLE3.[Office Code],
SOMETABLE3.[Item Count] ,
SOMETABLE3.[Item Revenue]
FROM SOMETABLE3
GROUP BY
SOMETABLE3.[Office Code],
SOMETABLE3.[Item Count] ,
SOMETABLE3.[Item Revenue]
) x3
on x1.[Office Code] = x3.[Office Code]
Lowell
February 9, 2007 at 1:01 pm
in practice, if it have so many join, it will really slow.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply