One Response for Stored Procedures

Featured Article(s)
TSQL 103 Part 2 of 2
Getting started with Stored Procedures, the benefits of them and much more.

One Response for Stored Procedures
So far I have only received one response to the previous editorial, "Are Stored Procedures the Only Way to Go?"

Scott replies in much detail why he prefers to use stored procedures compared to inline code. Most of his arguements can be resolved with a good separation in your application code where your SQL code is actually in a project of it’s own, and performs like a device driver through an interface.

For example, using an Interface called IPersistence, you could write different implementations of persistence for different engines. This would be a good practice anyway for applications where you are importing and exporting data. Those individual implementations for say Oracle, SQL Server, Cache, XML Export, etc. would be tested with automated unit testing software such as NUnit.

Let me share Steve’s imput with you. He has a number of techniques making the use and management of stored procedures much easier.

Steve, like me, likes lists. Here is his list for why he uses stored procedures:

Interesting post on inline SQL versus stored procs.

I implemented inline SQL in the first web app I helped write, and have used stored procedures since then, and I hope never to have to go back. My reasons?

1. When I spend a morning writing a stored proc script, I can put a comment section after the END statement and in that comment section I can place any number of sample calls to the procedure for unit testing. Bingo–reusable unit tests plus usage documentation, right with the stored proc, easily changeable when the proc changes, source-archivable with the proc, and all done before lunch. How do you set up unit tests for inline SQL?

2. Stored procedures allow an interface onto which you can easily script a test harness to test all of the database code separately for both correctness and performance. How do you attach a full data layer test harness to inline SQL?

3. Stored procs implement an agreed upon interface that not only includes natural strongly-typed parameter validation and "parsing", but allows separation of team roles. On my last project, there was enough SQL work to do that I worked only in the data layer and another guy did the ASP.NET UI layer. And we liked it that way.

4. With stored procs you can source-archive, change-control, and use-case-document the data layer separately. And sometimes deploy to production separately too. If someone asks me to document every operation against the database, I start by pulling out the stored proc list, and that’s half the battle. For inline SQL, what do you do? A full text search of all code?

5. If the database ever had to be swapped out for another vendor’s database, again, inline SQL would prompt an audit of all application code for any SQL syntax that needed to change for the new vendor’s particular version of SQL, and any changes would have to be tested THROUGH THE UI instead of unit tested right within the data layer of the newly installed database server.

6. Stored procs are modular and therefore reusable within an app or even ANOTHER app. How do you refactor your inline SQL to be reusable across applications? An assembly library simply containing classes that only contain inline SQL logic?

7. The apps I’ve worked on where the UI was written first and (often inline) SQL and data layer schema was written as-you-go turned out to be BAD, buggy, poor-performing, hard-to-test, hard-to-functionally-enhance designs. In one case, a guy got fired for a nearly-failed project. Apps I’ve worked on where I or someone else designed and tested the data layer as a layer, and put the UI on top of it, turned out to be SOLID designs that started with and kept good performance and for the most part were easy to test and add to. And what’s more expensive in a project, initial coding, or testing, debugging, maintenance, and tuning? The latter, every time. Proper app layering can help reduce that cost.

Cheers,

Ben