return

Description

Exits from a batch or procedure unconditionally and provides an optional return status. Statements following return are not executed.

Syntax

return [integer_expression] [plan "abstract_plan"]

Parameters

integer_expression

is the integer value returned by the procedure. Stored procedures can return an integer value to a calling procedure or an application program.

plan "abstract_plan"

specifies the abstract plan to use to optimize the query. The abstract plan can be a full or partial plan specified in the abstract plan language. Plans can be specified only for optimizable SQL statements, that is, queries that access tables. See Chapter 16, “Creating and Using Abstract Plans,” in the Performance and Tuning Guide: Optimizer and Abstract Plans for more information.

Examples

Example 1

If no user name is given as a parameter, the return command causes the procedure to exit after a message has been sent to the user’s screen. If a user name is given, the names of the rules created by that user in the current database are retrieved from the appropriate system tables:

create procedure findrules @nm varchar (30) = null as 
if @nm is null 
begin 
    print "You must give a user name" 
    return 
end 
else 
begin 
    select sysobjects.name, sysobjects.id,
    sysobjects.uid 
    from sysobjects, master..syslogins 
        where master..syslogins.name = @nm 
        and sysobjects.uid = master..syslogins.suid 
        and sysobjects.type = "R" 
end

Example 2

If the updates cause the average price of business titles to exceed $15, the return command terminates the batch before any more updates are performed on titles:

print "Begin update batch"
update titles
    set price = price + $3
    where title_id = 'BU2075'
update titles
    set price = price + $3
    where title_id = 'BU1111'
if (select avg (price) from titles
      where title_id like 'BU%') > $15
begin
      print "Batch stopped; average price over $15"
    return
end
update titles
    set price = price + $2
      where title_id = 'BU1032'

Example 3

This procedure creates two user-defined status codes: a value of 1 is returned if the contract column contains a 1; a value of 2 is returned for any other condition (for example, a value of 0 on contract or a title_id that did not match a row):

create proc checkcontract @param varchar (11) 
as 
declare @status int 
if (select contract from titles where title_id = @param) = 1 
    return 1 
else 
    return 2

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

return permission defaults to all users. No permission is required to use it.

See also

Commands begin...end, execute, if...else, while