combine in a single SP several SP’s

[codrutza]

SQL Server 2005
Hi
Please help me with this. I developed a report based on a SP, with subreports and it's running very slow. The subreports are each based on a SP as well.

How do I combine in a single SP several SPs with different selects?

For example
Report:
SELECT no, sales, … FROM table WHERE (table.Date >= @DateStart AND table.Date<@DateEnd)
Subreport1:
SELECT no, sales, …. FROM table WHERE table.comp=’A’ AND table.type=’Imp’ AND (table.Date >= @DateStart AND table.Date<@DateEnd)
Subreport2:
SELECT no, sales, … FROM table WHERE table.comp=’A’ AND table.type=’Imp’ AND table.via=’Air’ AND (table.Date >= @DateStart AND table.Date<@DateEnd)

……

 

[Olaf Doschke]

Did you measure what part of the report does take long? If so, did you properly index the tables? As you do filter for a data renage in the Date field, that should have an index. Indexing comp, type and via would also not hurt.

Also, what type of reports are you doing? Crystal Reports or SQL Server Reporting Services?

Bye, Olaf.

 

[dhulbert]

declare @datestart as datetime
declare @dateend as datetime

set	@datestart = '2011-01-01'
set @dateend = '2011-03-03'

exec sp1 @datestart, @dateend
exec sp2 @datestart, @dateend
exec sp3 @datestart, @dateend
exec sp4 @datestart, @dateend

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[codrutza]

Olaf
Crystal Reports. No, I didn't use indexes; I just do reports.
Dhulbert
I'm sorry but I don't understand

 

[SQLBill]

Codrutza,

You say you are putting several sp's (stored procedures) into one sp. But your example doesn't show stored procedures being used, it just shows scripts (SELECTs).

Dhulbert's example shows how to include several sp's (stored procedures) within one stored procedure (what you asked for).

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[dhulbert]

codrutza

If you are looking for help with Crystal reports then you should re-post the query in the crystal reports forum.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[codrutza]

I’m sorry, maybe I don’t say right.
I am trying to base a report, made with Crystal Report, on a sp; I developed already the report based on sp with several subreports based on sps, but it’s running slow.
The sp for the main report looks like this:
Create procedure proced @Year int
As
Declare @DateStart datetime
Declare @DateEnd datetime

Set @DateStart=cast(@Year-1 as varchar(4) )+’0101’
Set @DateStart=cast(@Year+1 as varchar(4) )+’0101’

SELECT no, sales, ... FROM table WHERE (table.Date >= @DateStart AND table.Date<@DateEnd)

The sps for the subreports look the same - have the same parameters, but other select like I wrote before.

In Crystal Reports I link trough parameter @Year.

I was asking if I can combine all the sp for the report and sps for the subreports in one, so I wouldn’t need the report+subreports

(I wrote in Crystal Reports forum as well)

 

[sblocher]

This may well be more of a Crystal question than a SQL question. If you can execute your current stored procedures outside of Crystal (say, in SSMS) and they perform well, then this is more of an issue of how the report is constructed than the sp(s).

Also don't know how, in Crystal, a stored procedure with multiple result sets can be be used in a report and subreports.

In the report, how many times might each of the subreports get executed? Are they executing for every line of output in the main report, for example? If so, (and this depends on how your report is setup), if you have 100 rows in your main report, and you execute one subreport for each row, then the subreport executes 100 times...which means the sp under that subreport runs 100 times. That's usually not a good model. And if you must have this model, then you must make sure the parameter from the row of the main report not only gets passed to the subreport, but gets passed all the way to the stored procedure.

Would need to know more details to help further.