Unconditional and scheduled execution

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.

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL/Transact SQL (T-SQL) SQL Server data conversions from date/time values to character types SQL and SQL Server Tutorial and Reference Guide How to use the SELECT statement in SQL Translating information requests into SQL SELECT statements SQL SELECT statement and SELECT query samples Using the ORDER BY clause of the SELECT query in SQL Using DISTINCT in SQL to eliminate duplicate rows Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005 How to configure Database Mail in SQL Server 2005 to send mail SQL Server stored procedures tutorial: Write, tune and get examples SQL/Transact SQL (T-SQL) Research SQL Server stored procedures SQL and SQL Server Tutorial and Reference Guide SQL Server stored procedures tutorial: Write, tune and get examples Check SQL Server database and log file size with this stored procedure SQL Server source code analysis and management adds database security Configure SQL Server Service Broker for sending stored procedure data Find size of SQL Server tables and other objects with stored procedure Track changes to SQL Server 2000 and 2005 with one simple utility Troubleshoot SQL Server 2005 temporary table performance problems Use SQL Profiler to find long running stored procedures and commands Stored procedure to monitor long-running jobs in SQL Server 2000 XML in SQL Server Manage traces in SQL Server 2005 Analysis Services with XMLA commands Create a computed column in SQL Server using XML data Create DDL table in SQL Server 2005 to audit DDL trigger activity Retrieve XML data values with XQuery in SQL Server 2005 XML data type in SQL Server 2005 vs. VARCHAR (MAX) SQL Server Blog Watch T-SQL commands vs. XML AUTO in SQL Server Basic Transact-SQL programming constructs: 15 tips, 15 minutes T-SQL identifiers Date, time and number data types in SQL Server

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary ACID  (SearchSQLServer.com) commit  (SearchSQLServer.com) DAO  (SearchSQLServer.com) fetch  (SearchSQLServer.com) OLE DB  (SearchSQLServer.com) query  (SearchSQLServer.com) SQL  (SearchSQLServer.com) T-SQL  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary