Microsoft® SQL Server 2008 R2 Unleashed

Creating Stored Procedures

To create a stored procedure, you need to give the procedure a unique name within the schema and then write the sequence of SQL statements to be executed within the procedure. Following is the basic syntax for creating stored procedures:

It is good programming practice to always end a procedure with the RETURN statement and to specify a return status other than 0 when an error condition occurs. Listing 28.1 shows a simple stored procedure that returns book titles and the names of the authors who wrote them.

Listing 28.1 A Sample Stored Procedure

Note

Unless stated otherwise, all examples in this chapter run in the ...