Stored Procedure Basics

Creating the Sproc: Basic Syntax

Creating a sproc works pretty much the same as creating any other object in a database, except that 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 is the 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 my experience, that’s the way most people

do it.) The name of your sproc must follow the rules for naming as outlined in Chapter 1.

After the name comes a list of parameters. Parameterization is optional, and I defer that discussion until

a little later in the chapter.

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

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 data.

I hope that, by now, you have the query that returns all the contents of a table down cold (Hint: SELECT

* FROM....)

USE AdventureWorks

GO

CREATE PROC spEmployee

AS

SELECT * FROM HumanResources.Employee

Not too rough, eh?

Now that you have your sproc created, execute it to see what you get:

EXEC spEmployee

You get exactly what you would have gotten if you had run the SELECT statement that’s embedded in

the sproc.

Changing Stored Procedures with ALTER

ALTER statements for sprocs work almost identically to views from the standpoint of what an ALTER

statement does.

The main thing to remember when you edit sprocs with T-SQL is that you are completely replacing the

existing sproc. The only differences between using the ALTER PROC statement and the CREATE PROC

statement are as follows:

❑ ALTER PROC expects to find an existing sproc, whereas CREATE doesn’t.

❑ ALTER PROC retains any permissions that have been established for the sproc. It keeps the same

object ID within system objects and allows the dependencies to be kept. For example, if procedure

A calls procedure B and you drop and re-create procedure B, you no longer see the dependency

between the two. If you use ALTER, it’s all still there.

❑ ALTER PROC retains any dependency information on other objects that may call the sproc being

altered.

Dropping Sprocs

It doesn’t get much easier than this:

DROP PROC|PROCEDURE <sproc name>

And it’s gone.