Best Practice, Stored Procedures & Datasets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay Sanderson - 31 Jan 2006 05:28 GMT

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

Reply to this Message

Kaisa M. Lindahl - 31 Jan 2006 09:24 GMT

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

> Hi,
>
[quoted text clipped - 8 lines]
>
> Jay

Reply to this Message

NH - 31 Jan 2006 10:16 GMT

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.

> 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
[quoted text clipped - 16 lines]
> >
> > Jay

Reply to this Message