Return to Stored Procedures: An Overview
Stored Procedures: An Overview
Written by Bill Graziano on 01 September 2000
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:
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.
CREATE PROCEDURE spCaliforniaAuthors
SELECT * FROM authors
WHERE state = 'CA'
ORDER BY zip
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:
You can check Books Online for more information on the GRANT statement.
GRANT CREATE PROCEDURE TO Development
You execute a stored procedure by typing it's name or using the EXECUTE statement. To execute our stored procedure you can type
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.