Unconditional and scheduled execution

Unconditional execution (the GoTo Statement) in T-SQL forces the server to continue the execution from a label within a stored procedure or batch. Scheduled execution (the WaitFor Statement) allows the developer to schedule a time for the remaining statements to be executed. Both are explained in the following excerpt.

Unconditional Execution: The GoTo Statement
The GoTo statement forces the server to continue the execution from a label:

GoTo label
...
label:

The label has to be within the same stored procedure or batch. It is not important whether the label or the GoTo statement is defined first in the code. The label can even exist without the GoTo statement. On the contrary, the server will report an error if it encounters a GoTo statement that points to a nonexistent label.
The following stored procedure uses the GoTo statement to interrupt further processing and display a message to the user when an error occurs:

Create Procedure dbo.ap_Lease_Close
-- Clear Rent, ScheduleId, and LeaseId on all assets associated
-- with specified lease.
     @intLeaseId int
As
     -- delete schedules
     Update dbo.Inventory
     Set Rent = 0,
         LeaseId = null,
         LeaseScheduleId = null
     Where LeaseId = @intLeaseId
     If @@Error <> 0 Goto PROBLEM_1

     -- delete schedules
     Delete from dbo.LeaseSchedule
     Where LeaseId = @intLeaseId
     If @@Error <> 0 Goto PROBLEM_2

     -- delete lease
     Delete from dbo.Lease
     Where LeaseId = @intLeaseId
     If @@Error <> 0     Goto PROBLEM_3
     Return 0

PROBLEM_1:
     Select 'Unable to update Inventory!'
     Return 50001
PROBLEM_2:
     Select 'Unable to remove schedules from the database!'
     Return 50002
PROBLEM_3:
     Select 'Unable to remove lease from the database!'
Return 50003

NOTE
The stored procedure is only an academic example. It would be better to use transactions and rollback changes in case of errors. I will describe transactions in Chapter 5.

Scheduled Execution: The WaitFor Statement
There are two ways to schedule the execution of a batch or stored procedure in SQL Server. One way is based on the use of SQL Server Agent. The other way is to use the WaitFor statement. The WaitFor statement allows the developer to specify the time when, or a time interval after which, the remaining Transact-SQL statements will be executed:

WaitFor {Delay 'time' | Time 'time'}

There are two variants to this statement. One specifies the delay (time interval) that must pass before the execution can continue. The time interval specified as a parameter of the statement must be less than 24 hours. In the following example, the server will pause for one minute before displaying the list of equipment:

WaitFor Delay '00:01:00'
     Select * from Equipment

The other variant is more significant. It allows the developer to schedule a time when the execution is to continue. The following example runs a full database backup at 11:00 P.M.:

WaitFor Time '23:00'
     Backup Database Asset To Asset_bkp

There is one problem with this Transact-SQL statement. The connection remains blocked while the server waits to execute the statement. Therefore, it is much better to use SQL Server Agent than the WaitFor statement to schedule jobs.


Basic Transact-SQL Programming Constructs

 Home: Introduction
 Tip 1: T-SQL identifiers
 Tip 2: Database object qualifiers
 Tip 3: Character string data types
 Tip 4: Date, time and number data types
 Tip 5: Special data types-Part 1
 Tip 6: Special data types-Part 2
 Tip 7: Local variables
 Tip 8: Global variables
 Tip 9: Table variables
 Tip 10: Flow control statements
 Tip 11: Blocks, looping and conditional statements
 Tip 12: Unconditional and scheduled execution
 Tip 13: Transact SQL cursors
 Tip 14: Cursor related statements
 Tip 15: Problems and justified use of cursors

The previous tip is from "15 tips in 15 minutes: Basic Transact-SQL Programming Constructs," excerpted from Chapter 3, of the book "Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET" by Dejan Sunderic, courtesy of McGraw-Hill Publishing.


This was first published in February 2007

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close