Creating a Stored Procedure

Now that you know what a stored procedure is, it is time to create your first stored procedure. Once the syntax has been covered, you will build a stored procedure in Management Studio that will return a set of rows and multiple sets of rows, and then you will move on to look at controlling the flow through your stored procedure. Some of the stored procedures built in this chapter will then be used in Chapter 16, when I demonstrate how to connect to SQL Server using different development languages and how to execute stored procedures.

The first task, though, is to take a look at the syntax.

CREATE PROCEDURE Syntax

Begin a stored procedure with a CREATE PROCEDURE statement. The CREATE PROCEDURE syntax offers a great many flexible options and extends T-SQL with some additional commands. The syntax generally appears as follows: