Are Stored Procedures Still Useful?
It used to be that stored procedures were considered must-haves. And there was little argument against the performance benefits of stored procedures so most of us used them whenever we could.
Now, the question of whether stored procedures are still good has come up in a couple of discussions and articles. Opponents of stored procedures argue that the performance benefits are no longer significant. Proponents of stored procedures have dropped the performance argument and have instead been focusing on the encapsulation benefits of stored procedures. I think if used correctly, stored procedures can hide the structure of the database from users and developers. Doing so makes it a lot easier to make changes to the structure without impacting downstream systems.
I believe the key to getting the most benefit from stored procedures is to be careful to not include business logic in them. Stored procedures should focus on retrieving and saving data. Nothing more.
On a related note, Microsoft's next version of SQL Server (code named Yukon) will apparently allow developers to write stored procedures using any .NET language. This ability will undoubtedly blur the line between data access logic and business logic. I'm also sure that some people will be inclined to write all their code right in the database. This will likely be a mistake and it'll certainly be interesting to see how things turn out.
> I believe the key to getting the most benefit from stored
> procedures is to be careful to not include business logic
> in stored procedures. Stored procedures should focus on
> retrieving and saving data. Nothing more.
That a bit overbroad. Certainly, I don't favor placing business logic in stored procedures; that often causes
maintenance hassles. But between 'retrieving and saving data' and 'business logic' lies a large realm of lower-level functionality that can benefit from centralizing it and making it available to all applications using the database. Standard formatting functions, functions to perform standard (but company specific) low-level
calculations used in business rules, for instance. Not the rules themselves; but the basic building block
workhorse functions we make the rules from.
>I think if used correctly, stored procedures can hide
> the structure of the database from users and developers.
> Doing so makes it a lot easier to make changes to the
> structure without impacting downstream systems.
True, but it's so rarely done well I've mostly gone off that idea for everyday use. Handy, even necessary
when transitioning from legacy systems, but too easy to mess up for everyday use.
I'm working with a database now where the work of 'hiding the structure of the database from developers' was done particularly badly. Because the encapsulation is imperfect, we're constantly patching and filling, and discovering yet another application or report that uses 'slightly different logic' to do some common operation, that breaks in interesting new ways when someone adds a new [blank] type to a lookup table.
> Microsoft's next version of SQL Server ... will
> apparently allow developers to write stored procedures
> using any .NET language. This ability will undoubtedly
> blur the line between data access logic and business
> logic. I'm also sure that some people will be inclined to
> write all their code right in the database. This is a
> mistake and it'll certainly be interesting to see how
> things turn out.
Badly, I'm sure, but people are already doing this. I have to maintain a system now that defines reports in
VBScript, and the VBScript is stored in a memo field.
Blurry and hard to maintain; the guy who designed it wrote a small program I've since extended to write and
test the VBScript, as it will run in the app.