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.


Related Articles

Handling SQL Server Errors (5 April 2010)

Testing with Profiler Custom Events and Database Snapshots (22 June 2009)

Debugging Stored Procedures in Visual Studio 2005 (25 June 2007)

Writing CLR Stored Procedures in C# - Returning Data (Part 2) (23 June 2005)

Writing CLR Stored Procedures in C# - Introduction to C# (Part 1) (6 June 2005)

Managed Data Access Inside SQL Server with ADO.NET and SQLCLR (31 May 2005)

An Evaluation of Stored Procedures for the .NET Developer (22 March 2004)

Run CLR code from a stored procedure (14 October 2003)

Other Recent Forum Posts

Filter query base on date field (3d)

Pull results from a stored procedure (3d)

Help With Query To Count Number of Pets & Vaccinations (4d)

Connect sql off network (4d)

Moving Registered Servers to a New Instance (8d)

CU 31 for 2017 Will Not Install (8d)

I get different results when I scan from a barcode reader with a different language (10d)

Spatial query in SQL server (12d)

- Advertisement -