Sams Teach Yourself Transact-SQL in 21 Days, Second Edition

Summary

Today you've learned how to write and execute stored procedures. Here is a quick summary of the full CREATE PROCEDURE syntax:

create proc[edure] <procedure-name>
[ ( @parameter-name datatype [= default-value] [output] [, …] ) ]
as
<SQL statements>
return [return-status]
[exec[ute]] [return-status = ] procedure-name
    [@parameter-name =] parameter-value [output] [, …]

In Day 16, you are going to look at some detailed programming and optimization issues associated with stored procedures. Until then, here is some general advice about writing stored procedures.

Do	Don't
Test procedures rigorously.	Use return status to return real data values (use output parameters instead).
Check parameters before using them.
Permit optional parameters where ...