Return Parameters

Another way that stored procedures can return information to the caller is through return parameters. The caller can then use conditional statements to check the returned value.

When both a create procedure statement and an execute statement include the output option with a parameter name, the procedure returns a value to the caller. The caller can be a SQL batch or another stored procedure. The value returned can be used in additional statements in the batch or calling procedure. When return parameters are used in an execute statement that is part of a batch, the return values are printed with a heading before subsequent statements in the batch are executed.

This stored procedure performs multiplication on two integers (the third integer, @result, is defined as an output parameter):

create procedure mathtutor 
@mult1 int, @mult2 int, @result int output 
as 
select @result = @mult1 * @mult2

To use mathtutor to figure a multiplication problem, you must declare the @result variable and include it in the execute statement. Adding the output keyword to the execute statement displays the value of the return parameters.

declare @result int
exec mathtutor 5, 6, @result output

(return status = 0)
 
Return parameters:
 
----------- 
         30

If you wanted to guess at the answer and execute this procedure by providing three integers, you would not see the results of the multiplication. The select statement in the procedure assigns values, but does not print:

mathtutor 5, 6, 32

(return status = 0)

The value for the output parameter must be passed as a variable, not as a constant. This example declares the @guess variable to store the value to pass to mathtutor for use in @result. SAP ASE prints the return parameters:

declare @guess int 
select @guess = 32 
exec mathtutor 5, 6, 
@result = @guess output

(1 row affected) 
(return status = 0) 
 
Return parameters: 
 
@result      
-----------  
        30

The value of the return parameter is always reported, whether or not its value has changed. Note that:

In the example above, the output parameter @result must be passed as “@parameter = @variable”. If it were not the last parameter passed, subsequent parameters would have to be passed as “@parameter = value”.
@result does not have to be declared in the calling batch; it is the name of a parameter to be passed to mathtutor.
Although the changed value of @result is returned to the caller in the variable assigned in the execute statement (in this case @guess), it appears under its own heading, @result.

To use the initial value of @guess in conditional clauses after the execute statement, store it in another variable name during the procedure call. The following example illustrates the last two bulleted items, above, by using @store to hold the value of the variable during the execution of the stored procedure, and by using the “new” returned value of @guess in conditional clauses:

declare @guess int 
declare @store int 
select @guess = 32 
select @store = @guess 
execute mathtutor 5, 6, 
@result = @guess output 
select Your_answer = @store, 
Right_answer = @guess 
if @guess = @store 
    print "Bingo!" 
else 
    print "Wrong, wrong, wrong!"

(1 row affected) 
(1 row affected) 
(return status = 0) 
 
@result      
-----------  
         30  
 
 Your_answer Right_answer  
 ----------- ------------  
          32           30  
                           
Wrong, wrong, wrong!

This stored procedure checks to determine whether new book sales would cause an author’s royalty percentage to change (the @pc parameter is defined as an output parameter):

create proc roy_check @title tid, @newsales int,
        @pc int output 
as 
declare @newtotal int 
select @newtotal = (select titles.total_sales + @newsales 
from titles where title_id = @title) 
select @pc = royalty from  roysched 
   where @newtotal  >= roysched.lorange and 
          @newtotal < roysched.hirange 
   and roysched.title_id = @title

The following SQL batch calls the roy_check after assigning a value to the percent variable. The return parameters are printed before the next statement in the batch is executed:

declare @percent int 
select @percent = 10 
execute roy_check "BU1032", 1050, @pc = @percent output 
select Percent = @percent
go

(1 row affected) 
(return status = 0) 
 
@pc          
-----------  
         12 
Percent      
-----------  
         12 
 
(1 row affected)

The following stored procedure calls roy_check and uses the return value for percent in a conditional clause:

create proc newsales @title tid, @newsales int 
as 
declare @percent int 
declare @stor_pc int 
select @percent = (select royalty from roysched, titles 
        where roysched.title_id = @title 
        and total_sales >= roysched.lorange 
        and total_sales < roysched.hirange
        and roysched.title_id = titles.title_id) 
select @stor_pc = @percent 
execute roy_check @title, @newsales, @pc = @percent
  output 
if 
  @stor_pc != @percent 
begin 
  print "Royalty is changed." 
  select Percent = @percent 
end 
else 
  print "Royalty is the same."

If you execute this stored procedure with the same parameters used in the earlier batch, you see:

execute newsales "BU1032", 1050

Royalty is changed 
Percent      
-----------  
         12 
 
(1 row affected, return status = 0)

In the two preceding examples that call roy_check, @pc is the parameter that is passed to roy_check, and @percent is the variable containing the output. When newsales executes roy_check, the value returned in @percent may change, depending on the other parameters that are passed. To compare the returned value of percent with the initial value of @pc, you must store the initial value in another variable. The preceding example saved the value in stor_pc.