Everybody loves stored procedures.  Over time, I’ve found that some common patterns emerge in stored procedure design.  These are a few of my favorites, mostly rendered obsolete by modern ORM technology.  I hope you enjoy these Straw Men, because they were fun to put together. 

The OBJECT RETRIEVAL Pattern

create proc GetOrder(@orderId int)
AS
    SELECT * FROM Orders WHERE OrderId = @orderId

This is a very simple form of the pattern.  In more advanced DBA-driven shops, this procedure might be auto-generated off of some master Entity-Relation diagram, and perhaps would include other information joined in that could, possibly, be useful under one or more situations.  A common example is joining to the Users table so you can pull in “CreatedByUserName.”  Auto-generating takes all the fun out of maintaining stored procedures, so most DBAs don’t go that route. 

The behavior this procedure holds is easily dealt with by any ORM you care to deal with, without any effort at all.

The LOOKUP Pattern

create proc GetProductTypes
AS
    SELECT ProductTypeID, ProductTypeName
    FROM ProductTypes
    ORDER BY ProductTypeName

Very similar to retrieval, this pattern is used to return that list of Name/Value pairs typically used to needed to populate a drop down list.  Encoding ordering details specific to the UI is common, more complex versions allow ordering to be defined by parameter or provide multiple copies of the stored procedure, ex. GetProductTypesOrderedByID, GetProductTypesOrderedByDate, …

As with the OBJECT RETRIEVAL pattern, any ORM can deal with this easily.  No reason to write this stored procedure, either. 

The UPSERT Pattern

create proc InsertOrUpdateOrder(
    @id int,
    @name nvarchar(50),
    [more parameters...]
) AS
    IF EXISTS(SELECT * FROM Orders WHERE OrderId = @id)
    BEGIN
        UPDATE Orders SET (...) WHERE OrderId = @id
    ELSE
        INSERT INTO Orders VALUES (...)
    END

Everybody’s favorite stored procedure, usually contains some sort of primitive error checking, and also returns the value of the newly created OrderID if applicable.  This type of procedure is (could be) commonly auto-generated, as there really isn’t much to think about here.  This is of course an overly simplistic form – better versions use a more intelligent check for existence.  This is such a common pattern that more advanced databases include a special UPSERT keyword to handle it.

Yeah ORMs handle this one easily too.  Bonus: business validation can be moved from your stored procedure where you can’t easily test it and have to deal with change management. Instead put that logic into imperative code where it belongs.

The BUSINESS LOGIC Pattern

This procedure is much more complicated than the others, encapsulating large amounts of logic into a language format that doesn’t lend itself to imperative statements. Some typical examples are handling things like CALCULATE_SALES_TAX or determining which is the most efficient shipping mechanism to fulfill an order.  Another common business logic procedure uses string contatenation (and a lot of string concatenation) to send emails via xp_send_mail. 

These procedures span hundreds or thousands of lines of dense and imperative-style SQL. (I’d provide an example, but I think there is a size limit to how big a blog post can be here.) 

Does the procedure work?  Well, an answer comes out of it…for goodness sake DON’T CHANGE ANYTHING or you’ll crash the entire inventory subsystem!

All of the other stored procedure patterns are a nuisance, but typically not dangerous.  This one is a monster.  Common problems are finding that an exception occurs early in the procedure and wasn’t handled correctly, resulting in a false answer.  Or, there’s a conditional that is always true somewhere around line 30, so the other 3000 lines of code are never executed.  Good times

Replace these with code under test, as soon as you can. 

The SET OPERATION Pattern

This procedure uses SQL to operate on sets of data, cleanly specifying what sort of transform is supposed to happen and what the resulting set of data will look like.  This could be fancy and use MDX to operate on cubes of data, or more commonly takes complex data structures and maps them against something simpler to fulfill a specific need.

Of course, ORMs can…hey wait a minute, this is the one where we’re playing to the stored procedure’s strength!   Too bad these procedures are so rare…

I hope you enjoyed this attempt to troll out all the DBAs in the audience.

Tony Rasa NHibernate, SQL