Professional SQL Server™ 2005 Programming

11.1. Creating the Sproc: Basic Syntax

Creating a sproc works pretty much the same as creating any other object in a database, exceptthat it uses the AS keyword we used with views. The basic syntax looks like this:

CREATE PROCEDURE|PROC <sproc name>
   [<parameter name> [schema.]<data type> [VARYING] [= <default value>] [OUT [PUT]][,
    <parameter name> [schema.]<data type> [VARYING] [= <default value>]
[OUT[PUT]][,
    ...
    ...
      ]]
[WITH
   RECOMPILE| ENCRYPTION |[EXECUTE AS { CALLER|SELF|OWNER|<'user name'>}]
[FOR REPLICATION]
AS
   <code> |EXTERNAL NAME <assembly name>.<assembly class>

As you can see, you still have the basic CREATE <Object Type> <Object Name> syntax that is the backbone of every CREATE statement. The only oddity here isthe choice between PROCEDURE and PROC. Either option works fine, but as always, I recommend that you be consistent regarding which one you choose. (Personally, I like the saved keystrokes of PROC, and in myexperience, that's the way most people do it.) The name of your sproc mustfollow the rules for naming as outlined in Chapter 1.

After the name comes a list of parameters. Parameterization isoptional, and I defer that discussion until a little later in the chapter.

Last, but not least, comes your actual code following the AS keyword.

11.1.1. An Example of a Basic Sproc

Perhaps the best example of basic sproc syntax is found in the most basic of sprocs — a sproc that returns all the columns in all the rows on a table — in short, everything to do with a table's ...