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.
