Parameter Defaults and an Overall Average Value

The next procedure returns the average price for all titles when no title_id is passed:

create proc pr_getprice3c
(@title_id char(6) = null)
as
if @title_id is null
begin
   select  avg(price) “avg price”
   from    titles
   return
end
select  price
from    titles
where   title_id = @title_id
return

*]pr_getprice3c

avg price
--------------------------
14.77

Should you always pass a null value as the default? No. Again, this depends on your application. Many parameters will have legitimate default values that should be applied. Parameters that use the bit data type should not default to null—bit variables and columns may never have the value null.

Let’s expand our procedure to permit it to change the price with an optional parameter. Here’s the procedure:

create proc pr_setprice
(@title_id char(6) = null,
 @price    money   = null)
as
if @title_id is null
begin
   print “The procedure pr_getprice3 requires a parameter, title_id”
   return
end
if @price is not null
begin
   update titles
   set    price = @price
   where  title_id = @title_id
   if @@rowcount = 1
      print “price changed”
end
select price
from   titles
where  title_id = @title_id
return

Here’s the execution:

pr_setprice PS2091, $15.97

price changed
price
--------------------------
15.97

The procedure accepts two optional parameters, but exits with a warning if the title_id is null. The server performs an optional update step if a non-null price is submitted. Note that, in this case, the price is returned whether it was changed or not.

Output Parameters

So far, you have used parameters to send data to the procedure. Now you will learn to return data from the procedure using an output parameter. You’ll use the price lookup routine to return the price to a calling procedure instead of the client application. Figure 16.1 shows how the client program, calling procedure, and inner procedure interact.

Figure 16.1.  One procedure can call another. They communicate using input and output parameters.

In order to set up this example, you will need to create two tables: items and orders. Here are the CREATE TABLE and INSERT scripts. If you can’t do this anywhere else, go ahead and create the objects in tempdb.

create table items
(item_id int not null,
price money null)
create table orders
(order_id int identity,
item_id int not null,
qty int not null,
unit_price money not null,
ext_price money not null)
insert items (item_id, price) values (1, $10)
insert items (item_id, price) values (2, $15)

Once the tables are set up, we can write the procedures. The outer procedure will insert a row in the orders table. It will call the inner procedure to look up a price for a particular item_id so it can calculate the unit_price and ext_price columns for the insert. Here is the inner procedure that does the lookup. It needs to use an output parameter (not a SELECT statement) to return results to the procedure rather than the client batch:

create proc pr_itemprice_lookup
(@item_id int, @price money output)
as
select @price = price
from   items
where  item_id = @item_id
return

You can execute this procedure directly from a SQL batch. You need to provide a local variable as a parameter in order to provide a space for the price to be returned.

declare @priceval money
execute pr_itemprice_lookup 1, @priceval output
select @priceval “price output”

price output
--------------------------
10.00

(1 row(s) affected)

When SQL Server executes the procedure, it retrieves the price of item number 1 and puts it into the @price parameter. Notice that no special work is required in the procedure to cause the value of @price to be returned to the calling batch. Also, notice that both the procedure and the execution require the keyword OUTPUT to establish the connection.

In our example, we wanted to write another procedure that called this inner procedure, retrieved the price for the item, then entered an order. Here’s that procedure:

create proc pr_insertorder
(@item_id int,
@qty int,
@order_id int output)
as
declare @priceval money
execute pr_itemprice_lookup @item_id, @priceval output
insert orders (item_id, qty, unit_price, ext_price)
values (@item_id, @qty, @priceval, @qty*@priceval)
select @order_id = @@identity
return

Let’s step through this procedure one step at a time.

create proc pr_insertorder
(@item_id int,
@qty int,
@order_id int output)

This section sets up the parameters for the procedure call. This procedure, in addition to inserting a new order, also returns the identity value (the automatic counter applied to the new row) to the calling program so the user will know the new order number.

declare @priceval money
execute pr_itemprice_lookup @item_id, @priceval output

Here is the call to the inner procedure. You need to declare a local variable to receive the output value, and you must designate the parameter as an output parameter.

insert orders (item_id, qty, unit_price, ext_price)
values (@item_id, @qty, @priceval, @qty*@priceval)

This is the heart of the procedure, in which the row is actually inserted into the table. The @priceval variable is referenced to determine both the unit price and the extended price.

select @order_id = @@identity

This statement stores the value of the identity counter (the automatic value used in the prior INSERT statement). Because the @order_id parameter was declared as an output parameter, it will be available to the calling program.

Let’s write the code to insert a row in the orders table using the procedure:

declare @order_number int
exec pr_insertorder 2, 15, @order_number output
select @order_number “Your order number”

Your order number
----------------
1

(1 row(s) affected)