You can create user-defined stored procedures in SQL Server 2005 by using TSQL
or with the .NET languages and the Common Language Runtime (CLR).
A stored procedure is a module of code that allows you to reuse a desired
piece of functionality and call that functionality by name. Another way to
look at a stored procedure is to view it as a routine that cannot be used in a
scalar expression. This is a crucial way in which a stored procedure differs
from a function (which you can use in a scalar expression).
A stored procedure is a database object in SQL Server 2005. You can assign
permissions to individual stored procedures to control who can execute them.
Optionally, a stored procedure may take one or more parameters. I show you
later in this chapter how to create and use stored procedures with and without
A user-defined stored procedure provides a way to store code, which you can
call from applications that you or developer colleagues create. Such stored
procedures offer security advantages. Text entered in, for example a Web
page, isn't executed directly but is passed as a parameter to a stored procedure.
This approach can prevent SQL injection attacks.
Types of stored procedure:
When using SQL Server 2005, you have several types of stored procedure
T-SQL user-defined stored procedures: A T-SQL stored procedure consists
of several T-SQL statements. It can, optionally, take one or more
input parameters and output one or more output parameters.
CLR user-defined stored procedures: A CLR stored procedure is a reference
to a method written by using one of the .NET Framework languages.
If you are unfamiliar with CLR in SQL Server 2005, read article Using the
Common Language Runtime in SQL Server. Like a T-SQL stored procedure,
it can optionally take one or more input parameters and/or output one or more
Extended stored procedures: An extended stored procedure is procedural
code often written in C, which runs in the SQL Server memory
space. These are now deprecated. However, existing extended stored
procedures are fully supported in SQL Server 2005. You can still create
extended stored procedures in SQL Server 2005 but Microsoft will drop
them in a future release of SQL Server. I recommend using CLR stored
procedures rather than creating new extended stored procedures. In
addition, if you have any extended stored procedures in existing applications,
you should replace them with CLR stored procedures when time
and resources allow.
What a stored procedure does
A stored procedure can, in principle, do anything that you can do using
either T-SQL or a .NET language. For example, you can use a stored procedure
to insert or update data in a table or you can use it to return data from
one or more tables to a client application.
In an online scenario, a user may specify a locality where they want to find a
company store. You can then pass the locality they choose to a stored procedure
as an input parameter.
Reasons to use a stored procedure
System stored procedures, which I describe in the next section, provide code
to support many SQL Server administrative tasks. Because these system
stored procedures are intended to support efficient execution of common
tasks, it usually makes good sense to make use of them.
Another important reason for using stored procedures is to reuse code for
tasks that you can use in several ways in a SQL Server database. If you later
make changes in the code, you can potentially make those changes in a single
place — inside the stored procedure. This helps to make maintenance of the
functionality in response to changing business circumstances an easier task.
Another advantage of stored procedures is that you can make changes in the
database layer in multi-tier applications without changing the client application.
You can change the code inside a user-defined stored procedure but, so
long as you don't alter the parameters it takes, you can leave the client application
If you use a stored procedure that uses a parameter, it provides a level of
protection against SQL injection attacks in a Web application. A SQL injection
attack uses T-SQL code entered by a user to access unauthorized information.
This can make SQL Server more vulnerable to future attack, as a
hacker gathers information about the structures used in SQL Server. By
using a stored procedure with a parameter, any information entered by a
user doesn't execute. If it is not appropriately structured for use as the
parameter, an ill-intentioned hacker is unlikely find out the structure of
your SQL Server installation.
A SQL injection attack can occur when you create an application that executes
dynamic T-SQL. Suppose you have a publisher that makes its catalog
available online. A form might include a text box where a user can insert an
author name. The application code might include code like this:
var sql = "SELECT * FROM Titles WHERE Author = '" +
SelectedAuthor + "'";
When the user enters a value of Andrew Watt, the T-SQL code returns titles
for that author. However, if a malicious user enters the following in the text
box, the Titles table is dropped.
DROP TABLE Titles --
This happens because the T-SQL code to be executed becomes
SELECT * FROM Titles WHERE Author = 'Andrew Watt';
DROP TABLE Titles --
The first line executes as desired by the application developer. The DROP
TABLE statement deletes the Titles table, which wasn't the expected effect
of accepting user input.
On the other hand, if you created a user-defined stored procedure that
accepts an Author parameter, the attempt to insert malicious code fails and
an empty rowset is returned because there is no author whose name is
Andrew Watt'; DROP TABLE Titles –.
System stored procedures
SQL Server 2005 provides a huge range of stored procedures, ready made for
your use. System stored procedures are stored in the Resource database. You
can use these system stored procedures for a broad range of administrative
purposes. System stored procedures can help you with several types of
tasks like Active Directory, Catalog stored procedures etc.