Home

	Expand Search

Transact-SQL User's Guide

Chapter 14 Using Stored Procedures

Returning information from stored procedures

Returning information from stored procedures

Stored procedures can return the following types of information:

• Return status - indicates whether or not the stored procedure completed successfully.

• proc role function - checks whether the procedure was executed by a user with sa_role, sso_role, or ss_oper privileges.

• Return parameters - report the parameter values back to the caller, who can then use conditional statements to check the returned value.

Return status and return parameters allow you to modularize your stored procedures. A set of SQL statements that are used by several stored procedures can be created as a single procedure that returns its execution status or the values of its parameters to the calling procedure. For example, many Adaptive Server system procedures execute a procedure that verifies that certain parameters are valid identifiers.

Remote procedure calls, which are stored procedures that run on a remote Adaptive Server, also return both kinds of information. All the examples below can be executed remotely if the syntax of the execute statement includes the server, database, and owner names, as well as the procedure name.

Return status

Stored procedures report a return status that indicates whether or not they completed successfully, and if they did not, the reasons for failure. This value can be stored in a variable when a procedure is called, and used in future Transact-SQL statements. Adaptive Server-defined return status values for failure range from -1 through -99; users can define their own return status values outside this range.

Here is an example of a batch that uses the form of the execute statement that returns the status:

declare @status int 
execute @status = byroyalty 50
select @status

The execution status of the byroyalty procedure is stored in the variable @status. "50" is the supplied parameter, based on the royaltyper column of the titleauthor table. This example prints the value with a select statement; later examples use this return value in conditional clauses.

Adaptive Server reserves 0, to indicate a successful return, and negative values from -1 through -99, to indicate the reasons for failure. Numbers 0 and -1 through -14 are currently used in version 12, as shown in Table 14-1:

Values -15 through -99 are reserved for future use by Adaptive Server.

If more than one error occurs during execution, the status with the highest absolute value is returned.

You can generate your own return values in stored procedures by adding a parameter to the return statement. You can use any integer outside the 0 through -99 range. The following example returns 1 when a book has a valid contract and returns 2 in all other cases:

create proc checkcontract @titleid tid 
as 
if (select contract from titles where 
        title_id = @titleid) = 1 
   return 1 
else 
   return 2

For example:

checkcontract MC2222

(return status = 1)

The following stored procedure calls checkcontract, and uses conditional clauses to check the return status:

create proc get_au_stat @titleid tid 
as 
declare @retvalue int 
execute @retvalue = checkcontract @titleid  
if (@retvalue = 1) 
   print "Contract is valid." 
else 
    print "There is not a valid contract." 

Here are the results when you execute get_au_stat with the title_id of a book with a valid contract:

get_au_stat MC2222 

Contract is valid 

Checking roles in procedures

If a stored procedure performs system administration or security-related tasks, you may want to ensure that only users who have been granted a specific role can execute it. The proc_role function allows you to check roles when the procedure is executed. It returns 1 if the user possesses the specified role. The role names are sa_role, sso_role, and oper_role.

Here is an example using proc_role in the stored procedure test_proc to require the invoker to be a System Administrator:

create proc test_proc
as
if (proc_role("sa_role") = 0)
begin
    print "You do not have the right role."
    return -1
end
else
    print "You have SA role."
    return 0

For example:

test_proc

You have SA role.

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. Adaptive Server 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 is displayed under its own heading, @result.

To use the initial value of @guess in conditional clauses after the execute statement, you must 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.

To pass values in the parameters use this format:

@parameter = @variable 

You cannot pass constants; there must be a variable name to "receive" the return value. The parameters can be of any Adaptive Server datatype except text or image.

If the stored procedure requires several parameters, either pass the return value parameter last in the execute statement or pass all subsequent parameters in the form @parameter = value.

A stored procedure can return several values; each must be defined as an output variable in the stored procedure and in the calling statements. The output keyword can be abbreviated to out.

exec myproc @a = @myvara out, @b = @myvarb out

If you specify output while you are executing a procedure, and the parameter is not defined using output in the stored procedure, you get an error message. It is not an error to call a procedure that includes return value specifications without requesting the return values with output. However, you will not get the return values. The stored procedure writer has control over the information users can access, and users have control over their variables.