I’ve been working on a project over the last week that involves fairly heavy use of Stored Procedures. It’s not a new technology and we’re not that new to it either, it’s just that during the development cycle the logic of Stored Procedures seems to have fallen off the radar.
Stored Procedures are perfect for us on a number of fronts. We run our entire website on a handful of files so the code base tends to be huge, moving some of that code out to a more appropriate home makes complete sense. There’s less possibility of an accident in the few thousand rows of code, and it also allows us to extend our code separation policy to new levels.
The creation of an abstraction layer between content and code also creates some interesting possibilities. Applications other than the website can execute the procedure and make use of the results, also developers can test queries without having to fish the SQL out of the code and replace all the variables, this often goes unmentioned but for me is one of the more useful aspects of the technology.
CREATE PROCEDURE sp_getproduct
@UnitsInStock SMALLINT = '10')
SET NOCOUNT ON;
SELECT [ProductName] FROM Products
WHERE ([UnitPrice] < @UnitPrice) AND ([UnitsInStock] > @UnitsInStock)