Which is better: Ad hoc queries or stored procedures? [closed]

by

In my experience writing mostly WinForms Client/Server apps these are the simple conclusions I’ve come to:

Use Stored Procedures:

  1. For any complex data work. If you’re going to be doing something truly requiring a cursor or temp tables it’s usually fastest to do it within SQL Server.
  2. When you need to lock down access to the data. If you don’t give table access to users (or role or whatever) you can be sure that the only way to interact with the data is through the SP’s you create.

Use ad-hoc queries:

  1. For CRUD when you don’t need to restrict data access (or are doing so in another manner).
  2. For simple searches. Creating SP’s for a bunch of search criteria is a pain and difficult to maintain. If you can generate a reasonably fast search query use that.

In most of my applications I’ve used both SP’s and ad-hoc sql, though I find I’m using SP’s less and less as they end up being code just like C#, only harder to version control, test, and maintain. I would recommend using ad-hoc sql unless you can find a specific reason not to.

More Related Contents:

  1. How to SELECT FROM stored procedure
  2. Function vs. Stored Procedure in SQL Server
  3. Passing an array of parameters to a stored procedure
  4. The maximum recursion 100 has been exhausted before statement completion
  5. How to assign an exec result to a sql variable?
  6. SQL Server SELECT into existing table
  7. How to check if a stored procedure exists before creating it
  8. Does Mysql have an equivalent to @@ROWCOUNT like in mssql?
  9. SQL : in clause in stored procedure:how to pass values
  10. SQL Server silently truncates varchar’s in stored procedures
  11. Can a stored procedure/function return a table?
  12. Split values over multiple rows [duplicate]
  13. How do I pass a list as a parameter in a stored procedure?
  14. Return setof record (virtual table) from function
  15. Must declare the scalar variable
  16. Ad hoc queries vs stored procedures vs Dynamic SQL [closed]
  17. How can I generate a temporary table filled with dates in SQL Server 2000?
  18. Stored procedure that exports data into csv files only exports to one file
  19. Insert Update stored proc on SQL Server
  20. SQL Server: should I use information_schema tables over sys tables?
  21. Comma separated values in a database field
  22. SQL CREATE LOGON – can’t use @parameter as username
  23. Inserting into Oracle and retrieving the generated sequence ID
  24. Converting String List into Int List in SQL
  25. How to use Alias in Where clause? [duplicate]
  26. What is the difference between a stored procedure and a view?
  27. Functions vs Stored Procedures
  28. How to execute a stored procedure inside a select query
  29. Bulk insert using stored procedure
  30. Call stored procedure with table-valued parameter from java

Leave a Comment