When are advantages using stored procedure over hard coded query?

Question

It is very common but important question for me as in my project I started converting hard coded query into stored procedure, and I know the advantage using of this. However, is there any specific scenario where I should avoid using Stored Procedure?

Answer 1

There is a standard blog post I send to everyone who thinks stored procedures always make sense except in very rare conditions. They generally cite a fallacy along the lines that you just need to make a law against killing people in order to stop murderers.

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

The link above lists a lot of really good arguments about when, and when not, to use stored procedures. Among them are that, especially in C#, you basically ignore everything the framework gives you, in terms of the power to have sensible UI decisions for queries, in favour of ... a maintenance nightmare.