Determine when to use stored procedures vs. SQL in the code

Tony Patton discusses the merits of stored procedures versus placing SQL directly in the code. Find out which one is best for your particular situation.

I once was pulled into a discussion about the merits of stored procedures versus placing SQL directly in the code. The atmosphere resembled a heated political debate, with each side presenting valid points. Here are both sides of the discussion.

Where should it go?

It's rare to tackle a development project that doesn't interact with a backend data source. After all, data is the lifeblood of an organization. When you create an application with a backend database like SQL Server, the Transact-SQL (T-SQL) programming language is the primary programming interface between your applications and the SQL Server database.

When using T-SQL programs, two methods are available for storing and executing the programs. You can store the programs locally and create applications that send the commands to SQL Server and process the results; or, you can store the programs as stored procedures in SQL Server and create applications that execute the stored procedures and process the results.

Stored procedures in SQL Server are similar to procedures in other programming languages in that they can:

  • Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
  • Contain programming statements that perform operations in the database, including calling other procedures.
  • Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

You make the decision of interacting with the backend database via stored procedures or by including the SQL in your application code. Let's take a closer look at each approach.

Take advantage of SQL Server

The benefits of using stored procedures in SQL Server rather than application code stored locally on client computers include:

  • They allow modular programming.
  • They allow faster execution.
  • They can reduce network traffic.
  • They can be used as a security mechanism.

You can create a stored procedure once, store it in the database, and call it any number of times in your program. Someone who specializes in database programming may create stored procedures; this allows the application developer to concentrate on the code instead of SQL. You can modify stored procedures independently of the program source code--the application doesn't have to be recompiled when/if the SQL is altered.

If the operation requires a large amount of T-SQL code or is performed repetitively, stored procedures can be faster than batches of T-SQL code. During creation, stored procedures are parsed and optimized, and you can use an in-memory version of the procedure after the procedure executes the first time. Each time a T-SQL statement from the client is run, SQL Server compiles, optimizes, and executes the statement.

You can perform an operation that requires hundreds of lines of T-SQL code through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.

And, you can grant users permission to execute a stored procedure even if they don't have permission to execute the procedure's statements directly.

Placing SQL in your code

While stored procedures offer a number of benefits, placing SQL directly in your application code isn't necessarily wrong. One excellent example is SQL that is generated on the fly. Its dynamic nature negates many of the advantages of a stored procedure.

That is, you cannot create and cache an execution plan since it's different each time it's called. In this scenario, you face assembling the SQL in your code or using the T-SQL exec command (or the sp_executesql system stored procedure). The difference with this scenario is where the code is executed (i.e., the server), so the final decision will depend on your environment.

Another situation in which you might have to decide between constructing dynamic T-SQL in your application or on the database server is the passing of data to the stored procedure. The dynamic nature of the database call implies that you must base your decision on data values and so forth. For instance, do you want to pass data values to a backend stored procedure, or use the values in the application code to construct the T-SQL string?

Choose your weapon

Both sides of this debate have valid points, but the final decision depends on your situation. Be sure not to be pigeonholed due to your lack of knowledge of another approach. For example, I've seen stored procedures that contain more than seven pages of T-SQL. You could break down the functionality easily with some of the processing handed to the application server and the stored procedure size reduced.

On the other hand, I know many developers who hate (or fear) T-SQL; they write their entire database calls in their application code. While this does work, it eliminates the database server from the picture. You should take advantage of the processing power of the database server when possible because it offloads processing from your application.

TechRepublic's free .NET newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET. Automatically sign up today!