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!
