A First Stored Procedure

Throughout this chapter, we are going to develop a procedure that returns the price of a title, based on its title_id. At the core of the procedure is a simple SELECT statement:

select price
from   titles
where  title_id = “PS2091”

If you execute the statement by itself (not as a procedure), you get this output:

title_id    price
--------    --------------------------
PS2091      10.95

(1 row(s) affected)

Let’s write the procedure using the CREATE PROCEDURE statement. Procedures are objects, so the procedure will require a name. The name must be unique among all objects in the database (so a procedure can’t have the same name as a table or view it accesses).

---

Tip:  You need to develop useful naming standards for procedures and then stick with them. Here are some suggestions for standards from the dozens I’ve come across over the years.

Have a procedure identifier at the start of the name, as in the following examples:

•  prTitlePrice

•  pGetPrice

•  pr_getprice

Use lower- and uppercase characters in a consistent way. If a new word should start with an uppercase letter, do so everywhere.

If you plan to use a verb in the procedure name (get, add), use that verb in the same way every time. Don’t start with get, use return, select, and find.

---

We’ll use pr_getprice for the name of the procedure. Here is the batch to create the procedure:

create   proc pr_getprice
as
select   price
from     titles
where    title_id = “PS2091”
return

This command did not return data, and it did not return any rows

First, let’s look at the procedure itself.

create  proc  pr_getprice
as

In the procedure creation, you can use the words proc and procedure interchangeably. After the name, you indicate that the actual batch of statements follows with the keyword AS.

select   price
from     titles
where    title_id = “PS2091”

This is the original query:

return

A RETURN statement is optional at the end of a procedure, but it’s strongly recommended. In the section titled “Procedure Return Status,” you will see how you can use the RETURN statement to let a calling program know whether the procedure worked properly.

Here’s the syntax for the basic version of the CREATE PROCEDURE statement you’ve seen so far.

create proc[edure] <procedure-name>
as
<SQL statements>
return

What about the output? SQL Server responds in the same way to all CREATE statements: total silence. No “procedure created” or “attaboys” here.

---

Note:  It’s even more bizarre in the command-line program, isql, than in windowing environments. Here’s a transcript of an isql session for a user creating the same procedure. (The numbers along the left side are line numbers from the program prompting the user to enter the next statement in the batch. The keyword GO is used to execute the query.)

C:\TEMP\sql>isql -Usa -P -Sautolukos
1> use pubs
2> go
1> create  proc pr_getprice
2> as
3> select  price
4> from    titles
5> where   title_id = “PS2091”
6> return
7> go
1>

The 1> on the last line means that the batch has executed and isql is ready for the next batch. The lack of an error message means that your procedure creation worked. In SQL Server, no news is good news.

---

Getting Information About Procedures

You may want to check to make sure that your procedure was really created. You can use SQL Enterprise Manager to see whether it appears in a list of procedures in the database, but if you don’t have SQL-EM, you may want to use a system procedure instead.

Information from sp_help

sp_help pr_getprice

Name               Owner     Type               When_created
------------------ -------- ------------------ ------------------------
pr_getprice        dbo       stored procedure   Dec 17 1996  3:38PM

(I had to trim the output again. The columns were way too wide to fit the width of the book.)

This procedure reports that a stored procedure named pr_getprice was created by the dbo on December 17, 1996.

---

Note:  dbo is a reserved name assigned to the owner of a database.

---

Information from sysobjects

Because a procedure is an object, you can also find it in sysobjects:

select name, id, type
from   syscolumns
where  name = “pr_getprice”

name                           id          type
------------------------------ ---------- ----
pr_getprice                    1024006679  P

SQL Server assigned a unique id to my procedure. (The id value you get may be different.) All procedures have the type P.

Information from sp_helptext

The system procedure, sp_helptext, returns the original code used to generate the stored procedure:

sp_helptext pr_getprice

text
----------------------------------------------------------------------
create proc pr_getprice
as
select price
from   titles
where  title_id = “PS2091”
return

---

Tip:  The text returned by sp_helptext is seldom formatted as neatly or correctly as this example. In fact, more often than not, it will take you a few minutes or more to fix a procedure if you count on this text. The problem is that SQL Server stores the text of a procedure in a sequence of 255-byte varchar columns, breaking the text up without regard to whether that break point falls in the middle of a word.

What’s more, you can create a procedure WITH ENCRYPTION, which prevents the server from storing the original source code at all. In this case, there is no source code to turn to.

To save yourself a lot of grief and to make life better, save the script of stored procedures, triggers, views, table creations, and just about every other thing you do in separate script files on a file system that you back up regularly. This will save your trying to reconstruct (or worse, remember!) a vital stored procedure.

---