What is the downside of using stored procedures all the time?

I read a book on SQLServer 2008. Within this book the author stated that although stored procedures are mostly the solution, you should avoid using them all the time.

I know that stored procedures are pre-compiled which as a result makes them run faster than normal commands. Also because they use parameters for passing data, they're far safer than normal SQL comma开发者_开发技巧nds in case of SQL injection attacks.

So what I don't understand is: Why not always use stored procedures?

A good article on the subject

http://www.codinghorror.com/blog/2004/10/who-needs-stored-procedures-anyways.html

So I think you should do what you prefer. There is no performance difference (for msot of the query you'll have to run).

I'd say go for no stored procedure : stored procedure are a pain in th a.. :

• no overloading : If you want to add a parameter you'll have to update all your calls (or create a new SP)

• no complex type : with dynamic sql you can build all your sql filter like you want depending on your complex objects

• securiy is not a reason : if your sql query are sql injection proof and your database is not available for everybody, you can handle your data access security policy at the application level (any dba would kill me saying this, but any dev would agree... I guess)

SP are "pre-compiled" (at the first execution, the database server will find the best execution plan, for SQL server), BUT in our time we can forget about it, the "compilation" time is really little so we don't have to worry about it. I never saw a case when I thought "OMG the compilation time is my application bottleneck", most of the time your application bottleneck will be the query itself, so don't worry about performance when you don't have to.

And this "pre-compilation" depends on the parameters you send to the SP (on the first call), so sometimes you can have a lot of performance problem (called "parameter sniffing") with SPs (see here :http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html).

I haven't written a stored procedure in almost 18 months because all my SQL calls are basically done with LINQ using the ADO.NET Entity Framework.

The benefits of using a SPROC as opposed to using LINQ for example are that, simple changes to a SPROC don't require you to recompile and publish a new build.

SPROCs aren't the most "readable". What I mean by that is, if you have a call to: "GetData" in your code, you have to actually go open SQL Server and look at what GetData is doing, as opposed to just looking at the LINQ code to see exactly what data is being returned.

Also, don't ever let anyone tell you that SPROCS are faster because they are compiled or pre-compiled. It's a myth. They aren't.

If you for some reason really have to dynamically generate SQL queries it can be easier to do that from client code than from within a stored procedure.

Some parts of your application should not go in to stored procedures; you can usually tell because you spend more time writing code to prepare and send meta-data about your application's state to the SP than you do writing the SP--but this is just poorly thought out design.

I think what the author may have been meaning is that you should not just use stored procedures for everything, not that you should use inline SQL instead of stored procedures. I certainly never recommend inline SQL.

Stored procedures tend to get big sometimes.
And they are really hard to debug.
If you got a SP containing hundreds lines of code it difficult to find bugs in there.

I don't use SPs for really simple queries. For example, if I want to show the user all the categories being used in our application, I'll just write SELECT CategoryName FROM Categories rather than make an SP just for that 4 word query.

However, anything that takes any input whatsoever is an SP, no matter how simple it is.

They are very good for a lot of processing - they are sometimes faster, although not always, depending on the structure of your data and processing. For loading data, or changing data based on user input, SPs are the preferred route, without a doubt.

However, there are situations where you need to do some data processing, that does not involve user data, that may involve a lot of work writing SPs to do things that you might be able to generate dynamic SQL better.

So I would say that SPs should be most of the time. But do not make it an unbending rule, because there are times when it isn't the correct solution.

I would say SPs are almost always the way to go, but it's possible that the stored procedure is not written well for an intended task. My counter-answer would be to write a different stored procedure for the intended task.

The only time I've had issues writing stored procedures for stuff is administrative tasks that could involve dynamic sql or multiple batches.

I prefer to use Stored Procedures. Especially because some of mine are very complicated. You can set optional parameters by using where (@PARAM IS NULL OR [FIELD] = @PARAM), so that helps clear that issue, and create dynamic queries. I like debugging queries in SQL as well. I understand the rest of the cons though.

Use what you're comfortable with, and what gets the job done! If you want to use all SP's, then do it. If you want to hard code them--do it... but good luck.

None really.

If you have a situation where you don't want the plan to be cached because of parameter sniffing you can use the RECOMPILE hint on the procedure or individual statements.

Only case I can think of would be a particularly complex dynamic search condition query that may have too many permutations to make separate procedures or statements for and might be easiest to generate in your client language rather than generating in TSQL and using EXEC / sp_executesql

Or obviously you wouldn't bother creating stored procedures for entirely adhoc queries.