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

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

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

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 last published in February 2007

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



Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.