What Is a Stored Procedure?

for RuBoard

A Transact-SQL stored procedure is a set of T-SQL code that is stored in a SQL Server database and compiled when used. You create this set of code using the CREATE PROCEDURE command. You can use most Transact-SQL commands in a stored procedure; however, some commands (such as CREATE PROCEDURE, CREATE VIEW, SET SHOWPLAN_TEXT, SET SHOWPLAN_ALL, and so forth) must be the first (or only) statement in a command batch, and therefore aren't allowed in stored procedures. Most Transact-SQL commands behave the same in a stored procedure as they do in a command batch, but some have special capabilities or exhibit different behavior when executed within the context of a stored procedure. Listing 1-1 shows a simple stored procedure (only the code from the CREATE PROCEDURE line down to the ensuing GO actually constitutes the stored procedure):

Listing 1-1 A simple stored procedure.

 Use Northwind GO IF OBJECT_ID('dbo.ListCustomersByCity') IS NOT NULL   DROP PROC dbo.ListCustomersByCity GO CREATE PROCEDURE dbo.ListCustomersByCity @Country nvarchar(30)='%' AS SELECT City, COUNT(*) AS NumberOfCustomers FROM Customers WHERE Country LIKE @Country GROUP BY City GO EXEC dbo.ListCustomersByCity 

for RuBoard