SQL Server 2005 Stored Procedures Tutorial

Part 1

I've been using databases for years, and I have a good knowledge of SQL, but for some reason I always viewed Stored Procedures as being only for the experts. But nothing could be further from the truth. In fact, if you really want to get some work done with SQL Server, and you want a robust system that separates out the user interface of your program from the underlying database logic, then you need stored procedures right now.

So what are stored procedures?

A stored procedure is a sequence of SQL command that are stored in the database and given a name. A stored procedure acts like a function in a programming language, taking parameters in and returning data as a result.

Here is a simple example of a stored procedure:

create procedure get_houses_by_color @color varchar(10)='red' as
select ID, name, location from houses
where color = @color

The first line tells SQL Server to make a new stored procedure called get_houses_by_color. This procedure is defined as having one parameter (called color) which is a varchar(10) defaulting to 'red'.

The second and third lines define the body of the procedure, which in this case is just a simple select command. See how the @color parameter is used in the body of the procedure, just like a variable in a programming language.

To run the stored procedure, type:

exec get_houses_by_color 'blur'

and the procedure will be executed, returning as a table with three columns (ID, name, location) with one row for each house of the chosen colour. The result of this stored procedure is no different from what you would get if you just executed the select statement directly.

So what's the big deal?

Storing this SQL select statement as a stored procedure is good because:

1. You only need to write the procedure once, and you can then access it very simply just by using its name. Imagine if this was a complicated select command. Using just its name is like using a short-cut.
2. A stored procedure exposes a very simple interface to a query, and hides the query details from the user. Maybe the underlying database gets changed later, but as long as you change the stored procedure to match, people can keep using the stored procedure without any changes to their code.
3. You can hide the names of tables or exact details about them from your users, only allowing them access to the data through stored procedures. Any user can be given access to use the stored procedure, but only sysadmins, db_owners and db_ddladmins can create stored procedures.
4. Database performance can be improved because the stored procedure is compiled when it is first used, and doesn't need to be re-examined by SQL Server every time it is called.
5. They can be used to enforce data integrity rules and business logic. For example, if users can only add records to the 'houses' table via a stored procedure, then this procedure can perform any validation checks it like on the data before allowing the new records to be created.

Trying them out

If you make a stored procedure, then find a bug in it and try to define it again, you'll get an error. Use the following approach to check if it exists and drop it first, before defining it:

if object_id('myprocedure') is not null drop proc myprocedure
go

Where are stored procedures actually stored?

Stored procedures are stored in the system table called syscomments. You can view an existing stored procedure by typing:

exec dbo.sp_helptext 'myprocedure'
go

The command

sp_help

lists all stored procedures that are currently defined.

Specifying and using more than one parameter:

Multiple parameters can be passed to a stored procedure in one of two ways (a little like VB):

1. Specify each parameter in a fixed order, e.g. exec myprocedure 'x', 'y'
2. Pass the parameters by name, e.g. exec myprocedure @param1='x', @param2='y'. This has the advantage that you can pass the parameters in any order, or you can omit one or both of them so that the procedure uses its default value.

What can they do for me?

Stored procedures can do anything that SQL statements can do, and a little more besides. You can use them to add new records, perform queries, update data and delete data.

Read on...

In part 2 of this article, I give many example stored procedures to demonstrate their power.