Stored Procedures: An Overview

By Bill Graziano on 1 September 2000 | Tags: Stored Procedures

This article covers the basic of writing a stored procedure. It's the first in a series on writing stored procedures.

A stored procedure is written using Transact-SQL (T-SQL). T-SQL is a subset of ANSI SQL-92 that has extensions to the Standard. T-SQL includes variables, conditional logic, loops and flow control. We'll use the pubs database for our examples. A stored procedure allows you to put code or business logic on the database server.

A stored procedure allows for more modular programming. You can create reusable, discrete pieces of functionality using stored procedures. Stored procedures have the parsing and execution plan built at the time they are created so they execute faster than dynamic SQL. If you have a stored procedure that manipulates large amounts of data, that data will remain on the server and not be transported across the network. You can also use stored procedures for security. You can grant users execute permissions on stored procedures without granting them permissions on objects manipulated by those stored procedures.

You can use SQL Server's Enterprise Manager to create and edit stored procedures. A simple stored procedure looks like:

CREATE PROCEDURE spCaliforniaAuthors AS SELECT * FROM authors WHERE state = 'CA' ORDER BY zip

This stored procedure is called "spCaliforniaAuthors". All it contains is a SELECT statement. All stored procedures that SQL Server provides start with "sp_" (and "xp_" for extended stored procedures) and I chose to almost follow this convention for this stored procedure. If you try to call a stored procedure that starts with "sp_" SQL Server will first search the MASTER database before searching the current database.

By default, only members of the dbo_owner role and db_ddladmin role can create stored procedures. Members of the dbo_owner role can give other users the ability to create procedures using a GRANT statement. That might look something like this:

GRANT CREATE PROCEDURE TO Development

You can check Books Online for more information on the GRANT statement.

You execute a stored procedure by typing it's name or using the EXECUTE statement. To execute our stored procedure you can type

EXECUTE spCaliforniaAuthors

This will execute the stored procedure and return the results. If you are calling this procedure from an ASP page (or other client) you can use the EXECUTE statement as you SQL string to execute. In this case, our stored procedure will return a record set.

That's enough for now. Over the next week or two I'll cover all aspects of writing stored procedures. Enjoy.