Transact-SQL User's Guide
|Chapter 14 Using Stored Procedures|
|Returning information from stored procedures|
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.Passing values in parameters
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.The output keyword
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.