Move away from stored procedures or not?

Jimmy Nilsson asks on his weblog if I've dropped stored procedures for everything, according to my dynamic query blog. Well, no, I haven't. The reason for this is simple: you should use the technology that fits the job best. If a couple of actions are simple in SQL and hard in C# or other 3GL (which is highly likely, since SQL is set-based, C# is imperative), why not write that code in SQL?

However, the occasions in which you should decide to use SQL instead of an imperative language are rare, since the vast majority of database activity is related to the 4 CRUD actions: Create, Retrieve, Update and Delete. In other words: the database activity is the end of a chain of logical components (CUD) or the start of a chain of logical components (R) and not a chain of logical components itself. This implies that it is not that important in what form you realize these CRUD actions: all in SQL procedures or in an imperative language with dynamically formulated queries. In fact, in some areas (UD), an imperative language with dynamically formulated queries is more efficient, because it can tailor the SQL statement to the task it has to perform, while the SQL procedures are inflexible due to their compiled nature and because they form an API that is called, which interface is not changing a lot.

At the moment I only use stored procedures for some retrieval actions actually, since these can be complex and can be hard to formulate using a dynamic query engine. I'm not referring to SELECT statements with joins but SELECTS with aggregate functions, resultset columns which get their data from a function call so on and so on. It's hard to define a set of predicate classes which are generic towards most databases plus have the same expressional power as SQL statements as SELECT function_f(Foo.Column3) as Column1, Foo.Column2 FROM Foo WHERE Foo.Column4 = function_b(Foo.Column5). It's then better to choose for those particular cases the SQL route and to write those functions / procedures in SQL stored procedures so you can have the best of both worlds.

Isn't that going to become a mess, both embedded, dynamic SQL and stored procedures? No I think it isn't. The same as having 20 libraries in your solution isn't going to become a mess because you've split the code up into 20 libraries and not 1. Having part of the code of the application inside the RDBMS isn't going to create a mess either, there is just a part of the code stored in the RDBMS, like there is some part of the application's set of datastructures stored in the RDBMS instead of in memory / the CLR, using constructions not known by the imperative language.

Views
However I think I'll move away from stored procedures in the future completely though, since more and more databases are supporting views. Views are awesome. Most people don't use them, but they have so much power in them. Once you've used them occasionally, you'll understand the true power that is embedded in the concept of views. Views are simply virtual tables which are defined using a SELECT statement with whatever resultset you want, even new columns based on calculated results. Views can make complex SELECT statements simple and easy to understand and are very beneficial in the world of dynamic queries, because you can hide complex SQL statements in the target views you select rows and columns from using those dynamic formulated queries. Views are also very clean: they are usable as regular tables, thus an extension on the set of tables, which opens up new ways of defining database logic: you can build new logic on top of a virtual table which is formed by database logic in a clean, simple way, which was impossible with a set of retrieval stored procedures, since you can't join with or select columns from stored procedures.

So to sum it up: stored procedures can be a good choice for complex SQL-related actions that are easier formulated in SQL than in an imperative language. Stored procedures are also a good choice when you have a complex retrieval query because it is hard to define a generic set of predicate classes for dynamic queries which have the same power as their SQL counterparts. Views are however an even better choice, since you can then have a well defined set of tables (virtual and real) and select rows and columns from these tables using dynamically defined queries, so both worlds benefit from this. I highly recommend views to anyone who is now struggling with complex retrieval queries constructed in stored procedures.