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 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.
