It is an exaggeration to say that I like stored procedures. They are an essential if somewhat dangerous part of the Sybase and SQL Server landscape, rather like a volcano, bog or swamp. If you use a stored procedure in the same way as a procedure in any other language, you soon end up in the jungle. Nothing quite works the way that your IT education would lead you to expect.

In the beginning was the SQL batch, merely a sequence of SQL queries and statements. It wasn’t recursive, and wasn’t parameterized; it was just a unit of work. The application had to throw the batch together as ‘ad-hoc’ or ‘dynamic’ SQL and execute it, receiving one or more results. The stored procedure improved on this by providing parameters, storing the batch on the server in compiled form, from where it could be invoked by name. It should have been called a stored batch; calling it a ‘procedure’ inflated programmers’ expectations too much.

As in interface component, called by the application, stored procedures work very well. You can receive as many results as you want, pass values to and fro in parameters, check the return code and read the message stream. What’s more, you don’t have to be too attentive to changes in the data model just as long as you keep the stored procedure in step, so that it continues to work in the same way for the application.

The first problem was that you can, from a batch, receive only one result from a stored procedure, and that is by using a rather nasty EXEC...INSERT syntax. The next problem is that this EXEC...INSERT can’t be nested. A more insidious problem is that, as stored procedures get bigger, they become more difficult to maintain. For anyone used to the consummate ease of debugging an IDE, the pain of working on a stored procedure is a shock. You have to take it apart to heal it. Then comes the problem of transactions. You can execute a stored procedure within a batch or another stored procedure. However, transactions aren’t nested, because in the traditional batch they didn’t have to be. You can put a transaction inside a transaction but it doesn’t do what you think, the result is that error-handling can be somewhat chaotic.

The instinct of any procedural developer is to create a rich hierarchy of methods, to structure the code into easily-managed reusable functionality. This doesn’t play well with a declarative language such as SQL, which specifies only what you want your result to be. Your SQL is merely an invitation to the query optimizer to create a plan for fetching the data. By creating a more broad-purpose procedure that deals with wildly different parameters, and even different tables, you throw all those advantages away because no single plan is likely to be optimal across the range of usage. You have code that looks great on code reviews but performs like a three-toed sloth.

We now have a range of routines, scalar functions, table functions, CLR functions, sp_executesql batches and we can, at last, pass table valued parameters to functions and procedures. Stored procedures are still as useful as ever but they aren’t a universal panacaea. If only Sybase hadn’t used the word ‘procedure’ when they invented itT-SQL.