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

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