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

Question

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.

I can't speak to anything other than SQL Server, but the performance argument is not significantly valid there unless you're on 6.5 or earlier. SQL Server has been caching ad-hoc execution plans for roughly a decade now.

I think this is a basic conflict between people who must maintain the database and people who develop the user interfaces.

As a data person, I would not consider working with a database that is accessed through adhoc queries because they are difficult to effectively tune or manage. How can I know what affect a change to the schema will have? Additionally, I do not think users should ever be granted direct access to the database tables for security reasons (and I do not just mean SQL injection attacks, but also because it is a basic internal control to not allow direct rights and require all users to use only the procs designed for the app. This is to prevent possible fraud. Any financial system which allows direct insert, update or delete rights to tables is has a huge risk for fraud. This is a bad thing.).

Databases are not object-oriented and code which seems good from an object-oriented perspective is can be extremely bad from a database perspective.

Our developers tell us they are glad that all our databse access is through procs becasue it makes it much faster to fix a data-centered bug and then simply run the proc on the production environment rather than create a new branch of the code and recompile and reload to production. We require all our procs to be in subversion, so source control is not an issue at all. If it isn't in Subversion, it will periodically get dropped by the dbas, so there is no resistance to using Source Control.

Stored procedures represent a software contract that encapsulates the actions taken against the database. The code in the procedures, and even the schema of the database itself can be changed without affecting compiled, deployed code, just so the inputs and outputs of the procedure remain the same.

By embedding queries in your application, you are tightly coupling yourself to your data model.

For the same reason, it is also not good practice to simply create stored procedures that are just CRUD queries against every table in your database, since this is still tight coupling. The procedures should instead be bulky, coarse grained operations.

From a security perspective, it is good practice to disallow db_datareader and db_datawriter from your application and only allow access to stored procedures.

Stored procedures are definitely the way to go...they are compiled, have execution plan before hand and you could do rights management on them.

I do not understand this whole source control issue on stored procedure. You definitely can source control them, if only you are a little disciplined.

Always start with a .sql file that is the source of your stored procedure. Put it in version control once you have written your code. The next time you want to edit your stored procedure get it from your source control than your database. If you follow this, you will have as good source control as your code.

I would like to quote Tom Kyte from Oracle here...Here's his rule on where to write code...though a bit unrelated but good to know I guess.

1. Start with stored procedures in PL/SQL...
2. If you think something can't be done using stored procedure in PL/SQL, use Java stored procedure.
3. If you think something can't be done using Java Stored procedure, consider Pro*c.
4. If you think you can't achieve something using Pro*C, you might want to rethink what you need to get done.

In our application, there is a layer of code that provides the content of the query (and is sometimes a call to a stored procedure). This allows us to:

• easily have all the queries under version control
• to make what ever changes are required to each query for different database servers
• eliminates repetition of the same query code through out our code

Access control is implemented in the middle layer, rather than in the database, so we don't need stored procedures there. This is in some ways a middle road between ad hoc queries and stored procs.

My answer from a different post: Stored Procedures are MORE maintainable because:

• You don't have to recompile your C# app whenever you want to change some SQL
• You end up reusing SQL code.

Code repetition is the worst thing you can do when you're trying to build a maintainable application!

What happens when you find a logic error that needs to be corrected in multiple places? You're more apt to forget to change that last spot where you copy & pasted your code.

In my opinion, the performance & security gains are an added plus. You can still write insecure/inefficient SQL stored procedures.

Easier to port to another DB - no procs to port

It's not very hard to script out all your stored procedures for creation in another DB. In fact - it's easier than exporting your tables because there are no primary/foreign keys to worry about.