Microsoft® SQL Server™ 2000 Unleashed, Second Edition

Creating and Executing Stored Procedures

To create a stored procedure, you need to give the procedure a unique name and then write the sequence of SQL statements to be included in the procedure. The following is the basic syntax for creating stored procedures:

create proc [owner.]procedure_name[; number] 
[[(]@parm_name datatype = default_value [output]
  [, ... ] [)]]
[with {recompile | encryption}]
as
SQL Statements
[return [integer_status_value]]

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.

Listing 28.1. A Stored Procedure That Returns Book Titles and the Names of the Authors Who Wrote ...