Is the usage of stored procedures a bad practice?

Stored procedures have been falling out of favour for several years now. The preferred approach these days for accessing a relational database is via an O/R mapper such as NHibernate or Entity Framework.

1. Stored procedures require much more work to develop and maintain. For each table, you have to write out individual stored procedures to create, retrieve, update and delete a row, plus a separate stored procedure for each different query that you wish to make. On top of that, you have to write classes and/or methods in your code to call into each stored procedure. Compare that with an O/R mapper: all you need to write are your class definitions, your database table, and a mapping file. In fact, modern ORMs use a convention-based approach that eliminates the need for a separate mapping definition.

2. Stored procedures promote bad development practices, in particular they require you to violate DRY (Don't Repeat Yourself), since you have to type out the list of fields in your database table half a dozen times or more at least. This is a massive pain if you need to add a single column to your database table. It isn't possible to pass an object as a parameter to a stored procedure, only simple types (string, integer, date/time etc) making it almost impossible to avoid huge parameter lists (a dozen or more).

3. Stored procedures promote bad configuration management practices. This arises from the argument that DBAs should be able to modify them independently of the code itself. Doing this results in a version of your code going into production that has never been integration tested, does not correspond to a single specific revision in source control, and may in fact not even correspond to any revision in source control at all. Basically, if you don't have an auditable record, end to end, of exactly which revision of your code is in production, you're going to run into trouble.

4. Stored procedures have to be deployed separately from the main body of your code. Unless you have a fully automated process in place to update them, there is a dramatically increased risk that they can get out of sync with your main codebase in one or more environments, introducing errors. This is especially problematic if you need to use your source control's bisect tool to track down the revision that introduced an error.

5. Stored procedures are inflexible. If you want to query your data in several different ways (different sort orders, lazy versus eager loading, paging, etc) you will need to write a multitude of separate stored procedures for all the different use cases, whereas ORMs give you a flexible, powerful query language (e.g. Linq to NHibernate).

6. Stored procedures require you to reinvent wheels. If you need optimistic concurrency, or a Unit of Work pattern, or lazy loading, or an Identity Map, or handling of parent/child collections, or caching, or class hierarchy mappings, or pretty much any of the other design patterns you read about in Martin Fowler's book, Patterns of Enterprise Application Architecture, you need to rebuild this functionality yourself from scratch, whereas an O/R mapper gives you all this, and more, straight out of the box. Very often, you will end up reinventing these wheels using copy and paste code, which again is a bad practice.

7. Stored procedures are difficult to unit test. With an ORM, you can mock your database code so as to be able to test your business logic quickly. With stored procedures, you have to rebuild an entire test database from scratch.

8. Stored procedures offer no performance advantage whatsoever. The (tiny) gains you get from passing only the name of the sproc over the wire as opposed to a SQL string are easily offset by the fact that you are all too likely to end up calling the same procedure two or three times with the same parameters in the same request, whereas an ORM would look in its Identity Map and say, "Hey, I've already retrieved that one, no need to do another round trip at all." Furthermore, the claim that stored procedures are cached on the server, whereas ad-hoc SQL is not, is a myth that was busted by Frans Bouma in his blog post, "Stored Procedures are bad, m'kay?"

9. Stored procedures offer little or no advantage in terms of security, and do not protect you against SQL injection vulnerabilities. Case in point:

create procedure GetUsers(@SortOrder nvarchar(50))asbegin    declare @sql nvarchar(100)    set @sql = 'SELECT * FROM Users ORDER BY ' + @SortOrder    exec @sqlend

Of course, you can write stored procedures that don't have SQL injection vulnerabilities, but you can equally write ad-hoc SQL in your business layer that doesn't have SQL injection vulnerabilities, by using parametrised queries. Attributing protection against SQL injection to stored procedures, rather than to not smashing SQL strings together, is a red herring and totally misleading.

I believe SP are good for calculations/data manipulation/report data sources in the DB.

When using it solely for data retrieveal/updates to table rows you will run into a whole world of hurt.

This is the approach followed by some data access layers, and data retrievel sps for an individual row can become a pain.

So no, i would not recomend this as the best way to go.

That's not a SP problem, that's an issue of your development process. If you have no information you need - just get it.

You can make a simple visual map that shows your table schema and dependant SPs. If your DB is too large for visual mapping, add common text file that consists of your SPs and names of tables it depends upon.

Anyway, the bigger your DB is, the worse will be your job when inlining details of your schema into your application code. When you use SP you guarantee that this functionality won't be doubled and that most changes will occur on DB side without application recompilation and redistribution.

Upd

I forgot to mention one other thing. Good DB tools provide easy way to find dependant tables for each SP. There's 'View Dependencies' item in SP context menu in Microsoft SQL Management Studio, for example.