Best Practice, Stored Procedures & Datasets

Hi,
Would be interested to hear your thoughts on whether is best to minimise or
maximise the use of stored procedures in SQL Reporting. Is it best to have
as much as possible coming from Stored Procedures or is it better to have as
little as possible. At this stage I am more concerned about management
rather than performance. However I'd love to hear arguments from all sides !
Cheers,
JayI prefer to use stored procedures as much as possible. There are two causes
for this: I think it's more efficient to let SQL Server handle the
processing needed to return the correct dataset and just use RS to do the
formatting, and it's usually easier to change the stored procedures later
than your report. But if you know you won't be able to access the SQL server
later, you should put the queries and logic in your RS report.
Kaisa M. Lindahl
"Jay Sanderson" <jay@.REMOVEMEacttiv.com> wrote in message
news:evEMmciJGHA.604@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Would be interested to hear your thoughts on whether is best to minimise
> or maximise the use of stored procedures in SQL Reporting. Is it best to
> have as much as possible coming from Stored Procedures or is it better to
> have as little as possible. At this stage I am more concerned about
> management rather than performance. However I'd love to hear arguments
> from all sides !
> Cheers,
> Jay
>|||Yes, I would recommend using Stored Procedures as much as possible as you can
usually reuse them in other reports. I find it particualarly useful to use
SPs for returning default paramaters as they are usually the same across most
reports I build and if you need to change any logic you just do it once and
you dont even need to republish the report or anything.
If you store the sql query in the report itself this will become
unmanageable as the amount of reports increase. Any small business logic
change will mean you have to trawl through all your reports to edit your
queries and then republsh the reports. SPs will save you all this hassle.
"Kaisa M. Lindahl" wrote:
> I prefer to use stored procedures as much as possible. There are two causes
> for this: I think it's more efficient to let SQL Server handle the
> processing needed to return the correct dataset and just use RS to do the
> formatting, and it's usually easier to change the stored procedures later
> than your report. But if you know you won't be able to access the SQL server
> later, you should put the queries and logic in your RS report.
> Kaisa M. Lindahl
> "Jay Sanderson" <jay@.REMOVEMEacttiv.com> wrote in message
> news:evEMmciJGHA.604@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > Would be interested to hear your thoughts on whether is best to minimise
> > or maximise the use of stored procedures in SQL Reporting. Is it best to
> > have as much as possible coming from Stored Procedures or is it better to
> > have as little as possible. At this stage I am more concerned about
> > management rather than performance. However I'd love to hear arguments
> > from all sides !
> >
> > Cheers,
> >
> > Jay
> >
>
>