Stored Procedures in SQL Server 2005

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).

 

Stored Procedure:

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

parameters.

 

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

available:

 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

output parameters.

 

 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

unchanged.

 

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.