Mark White's BI Blog

It's a discussion that comes up time after time in training session... when and why to use stored procedures. There are many heated debates posted on the net about this, with people seeming to argue strongly either for or against. Rather than suggest that you should or shouldn't use stored procedures with SQL server, let's look at their merits and pit falls with a view to knowing when to use them. The biggest misconception about stored procedures is that they offer significant performance advantages over submitted DML queries to the SQL engine. While it is true that the query plan for any stored procedures is precompiled, the SQL engine does a very good job of parameterizing DML queries and caching their plans. The real-world result is that stored procedures don't offer a significant improvement in performance – especially not in a world where disk I/O, no CPU, is the bottle neck. So then – if performance isn't a core criterion for choosing whether or not to use ...