Five Ways to Communicate

You can return information from a procedure to a calling program, a client application, or a user in any of five ways. It’s important to understand what those return methods are for, and how they are different.

•  SELECT is for data. Try to use it only to return data to the client application that’s intended for processing, printing, or displaying onscreen.

•  PRINT is for messages. Use it when you want to send a noncritical message to the user (not the programmer) about status, progress, or success.

•  RAISERROR is for errors. Use it when you want to send a critical message to the user about a failed process or invalid choice. Notice that this statement changes the value of @@error.

•  Output parameters return a value to a program (not a user). They are not shipped immediately to the client program; instead, they are returned to the program that called a stored procedure.

•  Return status tells the programmer whether a procedure worked or failed. Return status is only available to the program or procedure that calls a stored procedure.

Summary

Today you’ve learned all the syntax associated with writing and executing stored procedures. Here is a quick summary of the 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] [, …]

Tomorrow we will look at some detailed programming issues associated with stored procedures and then analyze a couple of sample procedures. Until then, here is some general advice about writing stored procedures.

Q&A

Q Where are stored procedures stored?

A A stored procedure is a database object, so it is stored in the database in which it is created. There are references to a stored procedure in several system tables, including sysobjects (one line per procedure), syscolumns (one line per parameter), sysdepends (one line per referenced table, view, or procedure), and syscomments (one line per block of 255 characters of source SQL).

Q Is a stored procedure faster than a batch?

A Tomorrow we’ll look more closely at procedure performance issues. For now, I’ll say, “Yes, most of the time.”

Q How do I get a list of all stored procedures in the system?

A On a database-by-database basis, you can execute this statement to retrieve a list of stored procedures:

select name
from sysobjects
where type = “P”

Q How do I get a list of parameters for a procedure?

A Use sp_help procedure-name.

Q Can I write a system stored procedure?

A Yes. You will learn how tomorrow.

Q What does “group number” refer to?

A If you try to create a procedure whose name already exists, you get this error message:

Msg 2729, Level 16, State 1
Object ‘oops’ group number 1 already exists in the database.
Choose another procedure name

Group numbering allows you to create sets of procedures with the same name, but different sequence numbers. For instance, to add a procedure oops to group 2, I would execute:

create proc oops;2
as
…

This creates a maintenance nightmare because (1) you can’t run any stored procedures against the procedure, and (2) you have to drop all of the procedures belonging to a group at once.

Don’t mess with it … it’s ugly.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you’ve learned. Try to understand the quiz and exercise answers before continuing on to the next day’s lesson. Answers are provided in the appendix.

Quiz

1.  What is the correct method to return a data value to a calling program?

2.  I have the following procedure and batch, but when I run the batch, the value of @out_x is always null. What’s wrong?

create proc p1 (@x int output)
as
select @x = 1
return
declare @out_x int
execute p1 @out_x
select @out_x

3.  In this procedure and batch, my return status is always null. Why?

create proc p2
as
declare @x int
select @x = 1
if @x = 1
   return 1
else
   return 0
declare @retstat int
execute p2
select @retstat “return”

Exercises

1.  Write a stored procedure to display the average dollar value of each sale to store number 7066.
This requires creating and using a temporary table! Here’s what I’d like to do, but SQL Server won’t let me:

select avg(sum(qty * price))
from sales s, titles t
where stor_id = “7131”
and t.title_id = s.title_id
group by ord_num

Luckily, it’s no big deal to create a temp table in a stored procedure and then drop it before you exit.

2.  Modify the stored procedure to allow the user to enter a store number as a parameter. Test your procedure. What do you notice about the parameter value?

3.  Modify the stored procedure again, making the store number parameter optional. If the user doesn’t pass a store number, provide the overall average sale value. (This step is kind of tough!)

4.  Finally, return the average value as an output parameter instead of selecting it. Use a return status value to warn the calling application if there are problems.