SQL Server Stored Procedure Basics

• By Ken Henderson
• Feb 8, 2002
• Sample Chapter is provided courtesy of Addison Wesley

This chapter is from the book
Guru's Guide to SQL Server Stored Procedures, XML, and HTML, The

Today, average software development practices are becalmed in a windless sea of code-and-fix programming—a kind of flat-earth approach to software development that was proven ineffective 20 years ago.

Working from the assumption that the human brain learns by associating new data with what it already knows, we'll spend this chapter building a base framework onto which we can assemble the knowledge conveyed by the remainder of the book. We'll touch on the topics covered in the book's other chapters, but we'll save the details for the chapters themselves. I'm assuming that you know some basic Transact-SQL with which we can associate these high-level concepts. We'll spend the remainder of the book filling in the details and expanding on what we cover here.

This chapter serves to prime the discussion on SQL Server stored procedure programming. It will tell you what a stored procedure is, how stored procedures are often used, and why and how you should use them. It will also jumpstart the treatment of Transact-SQL as a full-fledged programming language. If I could have you take one thing away from reading this book, it would be that Transact-SQL programming is very much like any other type of programming: It requires the same attention to detail, the same craftsmanship, and the same software engineering skill to do well.

What Is a Stored Procedure?

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