Using sprocs or ad-hoc sql

I use stored procedures "religiously" when developing data-centric applications and I'm always interested as to why people who use SqlServer would want to use ad-hoc sql; I think that the main reason is probably is the perception of "duplicating effort".

Anyway, I just read an interesting thread about the use of stored procedures in which the following list of reasons came up for using stored procs:

  • this way you can keep your SQL code out of your C# code
  • very often you can hide your changes to DB schema from your code by modifying only your SPROC
  • If you use particular query in many points of the application, you can reuse it
  • The SPROC is more efficient - your SQL is precompiled before first use and stored until DB restart or explicit recompile - and when you use ad-hoc queries, the DB engine has to preprocess query each and every time
  • You can easily use many different T-SQL language constructs, like conditionals, cursors etc.

Whilst they are true I think that the second point there is the most important one of those followed by point one.  The other 3 issues including the issues surrounding perf. are probably not really all that relevant - particularly the perf. issue.

For me, the biggest reason to use stored procedures has been for the security issues surrounding sql injection attacks, but as mentioned in the post that I read you can also use parameter placeholders so that you get the benefit of parameter checking and also be able to inline queries (but changing a query now requires a recompile of the whole app). 

    http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlCommandClassParametersTopic.asp?frame=true

Mind you, I still find that readability of stored procedures is better - and hence easier for me to maintain - when separated from code.

Original Post: http://weblogs.asp.net/Toffer/archive/2005/03/07/387700.aspx

4 Comments

  • Thanks Matt, I do recall when those lofty discussions were taking place last year... it's all good fun :-)

  • I think that there's a more important security consideration, and that is: "What level of access to your database do you give your applications?"



    If you're using stored procedures exclusively, you can give your applications an account that has execute permissions on the stored procedures that the application needs, and nothing else. You have, without qualification, limited the attack surface that that application has. If you're *not* using stored procedures, then you've got to allow select, and likely insert, update and delete privileges to the account that the application uses, and your attack surface is much, much larger. All of your data immediately becomes far more vulnerable, which is a very bad thing.



    So, in short, I agree with your choice whole-heartedly.

    <Grin />

  • Yes, security is an area that I'd find it hard to change my ways when I have something that already works (i.e.: is already secure).



    Another area for me is maintainability... there's something simple about telling somebody to "go and fix the GetUsers sproc" and have them go directly to the "Stored Procedures" node in Sql. If you have sql in your apps then you have to go looking for stuff a bit - not a nightmare I know, but it's still another thing that you'd have to find your way around.

  • uinstall any software db215b834d47b47764f0469c4c2a9aa5

Comments have been disabled for this content.