Control-of-Flow Language (Transact-SQL)

Control-of-Flow Language (Transact-SQL)

BEGIN……END

Encloses a series of Transact-SQL statements so that a group of Transact-SQL statements can be executed.

BEGIN and END are control-of-flow language keywords.

BEGIN 

     {  

    sql_statement | statement_block  

     }  

END

Arguments

{sql_statement| statement_block}

Is any valid Transact-SQL statement or statement grouping as defined by using a statement block.

Remarks

BEGIN...END blocks can be nested.

Although all Transact-SQL statements are valid within a BEGIN...END block, certain Transact-SQL statements should not be grouped together within the same batch, or statement block.

BREAK (Transact-SQL)

Exits the innermost loop in a WHILE statement or an IF…ELSE statement inside a WHILE loop.

Any statements appearing after the END keyword, marking the end of the loop, are executed. BREAK is frequently, but not always, started by an IF test.

CONTINUE (Transact-SQL)

Restarts a WHILE loop. Any statements after the CONTINUE keyword are ignored.

CONTINUE is frequently, but not always, opened by an IF test

END (BEGIN...END) (Transact-SQL)

Encloses a series of Transact-SQL statements that will execute as a group. BEGIN...END blocks can be nested.

BEGIN   

     { sql_statement | statement_block }   

END  

Arguments

{ sql_statement| statement_block}

Is any valid Transact-SQL statement or statement grouping as defined with a statement block. To define a statement block (batch), use the control-of-flow language keywords BEGIN and END. Although all Transact-SQL statements are valid within a BEGIN...END block, certain Transact-SQL statements should not be grouped together within the same batch (statement block).

Result Types

Boolean

ELSE (IF...ELSE) (Transact-SQL)

Imposes conditions on the execution of a Transact-SQL statement. The Transact-SQL statement (sql_statement) following the Boolean_expressionis executed if the Boolean_expression evaluates to TRUE. The optional ELSE keyword is an alternate Transact-SQL statement that is executed whenBoolean_expression evaluates to FALSE or NULL.

IF Boolean_expression

  { sql_statement | statement_block }  

[ ELSE

{ sql_statement | statement_block }

]  

Arguments

Boolean_expression

Is an expression that returns TRUE or FALSE. If the Boolean_expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses.

{ sql_statement | statement_block }

Is any valid Transact-SQL statement or statement grouping as defined with a statement block. To define a statement block (batch), use the control-of-flow language keywords BEGIN and END. Although all Transact-SQL statements are valid within a BEGIN...END block, certain Transact-SQL statements should not be grouped together within the same batch (statement block).

Result Types

Boolean

IF...ELSE (Transact-SQL):

Imposes conditions on the execution of a Transact-SQL statement. The Transact-SQL statement that follows an IF keyword and its condition is executed if the condition is satisfied: the Boolean expression returns TRUE. The optional ELSE keyword introduces another Transact-SQL statement that is executed when the IF condition is not satisfied: the Boolean expression returns FALSE.

IF Boolean_expression   

     { sql_statement | statement_block }   

[ ELSE   

     { sql_statement | statement_block } ]   

Arguments

Boolean_expression

Is an expression that returns TRUE or FALSE. If the Boolean expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses.

{ sql_statement| statement_block }

Is any Transact-SQL statement or statement grouping as defined by using a statement block. Unless a statement block is used, the IF or ELSE condition can affect the performance of only one Transact-SQL statement.

To define a statement block, use the control-of-flow keywords BEGIN and END.

Remarks

An IF...ELSE construct can be used in batches, in stored procedures, and in ad hoc queries. When this construct is used in a stored procedure, it is frequently used to test for the existence of some parameter.

IF tests can be nested after another IF or following an ELSE. The limit to the number of nested levels depends on available memory

GOTO (Transact-SQL)

Alters the flow of execution to a label. The Transact-SQL statement or statements that follow GOTO are skipped and processing continues at the label. GOTO statements and labels can be used anywhere within a procedure, batch, or statement block. GOTO statements can be nested.

Syntax

Define the label:  

Label:  

Alter the execution: 

GOTO label

Arguments

label

Is the point after which processing starts if a GOTO is targeted to that label. Labels must follow the rules for identifiers. A label can be used as a commenting method whether GOTO is used.

Remarks

GOTO can exist within conditional control-of-flow statements, statement blocks, or procedures, but it cannot go to a label outside the batch.

GOTO branching can go to a label defined before or after GOTO.

Permissions

GOTO permissions default to any valid user.

Examples

The following example shows how to use GOTO as a branch mechanism.

DECLARE @Counter int; 

SET @Counter = 1; 

WHILE @Counter < 10 

BEGIN  

    SELECT @Counter 

    SET @Counter = @Counter + 1 

    IF @Counter = 4 GOTO Branch_One --Jumps to the first branch. 

    IF @Counter = 5 GOTO Branch_Two  --This will never execute. 

END 

Branch_One: 

    SELECT 'Jumping To Branch One.' 

    GOTO Branch_Three; --This will prevent Branch_Two from executing. 

Branch_Two: 

    SELECT 'Jumping To Branch Two.' 

Branch_Three: 

    SELECT 'Jumping To Branch Three.'; 

RETURN (Transact-SQL)

Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed.

Syntax

RETURN [ integer_expression ]  

Arguments

integer_expression

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

Return Types

Optionally returns int.

Note: Unless documented otherwise, all system stored procedures return a value of 0. This indicates success and a nonzero value indicates failure.

Remarks

When used with a stored procedure, RETURN cannot return a null value. If a procedure tries to return a null value (for example, using RETURN @status when @status is NULL), a warning message is generated and a value of 0 is returned.

The return status value can be included in subsequent Transact-SQL statements in the batch or procedure that executed the current procedure, but it must be entered in the following form: EXECUTE @return_status = <procedure_name>.

THROW

TRY CATCH

WHILE (Transact-SQL)

Sets a condition for the repeated execution of an SQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords.

WHILE Boolean_expression   

     { sql_statement | statement_block | BREAK | CONTINUE }

Arguments

Boolean_expression

Is an expression that returns TRUE or FALSE. If the Boolean expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses.

{sql_statement | statement_block}

Is any Transact-SQL statement or statement grouping as defined with a statement block. To define a statement block, use the control-of-flow keywords BEGIN and END.

BREAK

Causes an exit from the innermost WHILE loop. Any statements that appear after the END keyword, marking the end of the loop, are executed.

CONTINUE

Causes the WHILE loop to restart, ignoring any statements after the CONTINUE keyword.

Remarks

If two or more WHILE loops are nested, the inner BREAK exits to the next outermost loop. All the statements after the end of the inner loop run first, and then the next outermost loop restarts.

WAITFOR (Transact-SQL)

Blocks the execution of a batch, stored procedure, or transaction until a specified time or time interval is reached, or a specified statement modifies or returns at least one row.

Syntax

WAITFOR  

{ 

    DELAY 'time_to_pass'  

  | TIME 'time_to_execute'  

  | [ ( receive_statement ) | ( get_conversation_group_statement ) ]  

    [ , TIMEOUT timeout ] 

} 

Arguments

DELAY

Is the specified period of time that must pass, up to a maximum of 24 hours, before execution of a batch, stored procedure, or transaction proceeds.

'time_to_pass'

Is the period of time to wait. time_to_pass can be specified in one of the acceptable formats for datetime data, or it can be specified as a local variable. Dates cannot be specified; therefore, the date part of the datetime value is not allowed.

The following example executes the stored procedure after a two-hour delay.

BEGIN  

    WAITFOR DELAY '02:00';  

    EXECUTE sp_helpdb;  

END;  

GO 

TIME

Is the specified time when the batch, stored procedure, or transaction runs.

'time_to_execute'

Is the time at which the WAITFOR statement finishes. time_to_execute can be specified in one of the acceptable formats for datetime data, or it can be specified as a local variable. Dates cannot be specified; therefore, the date part of the datetime value is not allowed.

The following example executes the stored procedure sp_update_job in the msdb database at 10:20 P.M. (22:20).

EXECUTE sp_add_job @job_name = 'TestJob'; 

BEGIN 

    WAITFOR TIME '22:20'; 

    EXECUTE sp_update_job @job_name = 'TestJob', 

        @new_name = 'UpdatedJob'; 

END; 

receive_statement

Is a valid RECEIVE statement.

WAITFOR with a receive_statement is applicable only to Service Broker messages. For more information, see RECEIVE (Transact-SQL).

get_conversation_group_statement

Is a valid GET CONVERSATION GROUP statement.

WAITFOR with a get_conversation_group_statement is applicable only to Service Broker messages.

TIMEOUT timeout

Specifies the period of time, in milliseconds, to wait for a message to arrive on the queue.

Specifying WAITFOR with TIMEOUT is applicable only to Service Broker messages. For more information, see RECEIVE (Transact-SQL) and GET CONVERSATION GROUP (Transact-SQL).

Remarks

While executing the WAITFOR statement, the transaction is running and no other requests can run under the same transaction.

The actual time delay may vary from the time specified in time_to_pass, time_to_execute, or timeout and depends on the activity level of the server. The time counter starts when the thread associated with the WAITFOR statement is scheduled. If the server is busy, the thread may not be immediately scheduled; therefore, the time delay may be longer than the specified time.

WAITFOR does not change the semantics of a query. If a query cannot return any rows, WAITFOR will wait forever or until TIMEOUT is reached, if specified.

Cursors cannot be opened on WAITFOR statements.

Views cannot be defined on WAITFOR statements.

When the query exceeds the query wait option, the WAITFOR statement argument can complete without running. For more information about the configuration option, see Configure the query wait Server Configuration Option. To see the active and waiting processes, use sp_who. Each WAITFOR statement has a thread associated with it. If many WAITFOR statements are specified on the same server, many threads can be tied up waiting for these statements to run. SQL Server monitors the number of threads associated with WAITFOR statements, and randomly selects some of these threads to exit if the server starts to experience thread starvation.

You can create a deadlock by running a query with WAITFOR within a transaction that also holds locks preventing changes to the rowset that the WAITFOR statement is trying to access. SQL Server identifies these scenarios and returns an empty result set if the chance of such a deadlock exists.

Including WAITFOR will slow the completion of the SQL Server process and can result in a timeout message in the application. If necessary, adjust the timeout setting for the connection at the application level.