SQL Server Series: Process Control statements

The statements in T-SQL that are used to write process Control modules are: BEGIN ... and statement, IF ... Else statements, case statements, while statements, goto statements, break statements, WAITFOR statements, and return statements.

1. BEGIN ... and statements

A statement block is a piece of code that consists of multiple Transact-SQL statements, allowing you to execute a set of Transact-SQL statements. Begin and end are keywords that control the flow language. BEGIN ... The end statement block is typically included in other control processes to complete code functionality that differs from one process to another.

Example:

DECLARE @count INTSELECT @count = 0 while @count < TenBEGIN    PRINT 'count =' + CONVERT(VARCHAR(Ten),@count)    SELECT @count = @count + 1ENDPRINT 'Loop finished, Count =' + CONVERT(VARCHAR(Ten),@count)

Execution Result:

01 2 345   67 8)910 

2. IF ... Else statement

IF ... The Else statement is used to make conditional judgments before executing a set of code, and to execute different code based on the result of the judgment. IF ... Else statement to determine the Boolean expression, if the Boolean expression returns TRUE, executes the statement block after the IF keyword, or, if the Boolean expression returns FALSE, executes the statement block following the Else keyword.

Grammar:

IF boolean_expression       |  [  ELSE      ]

Example:

DECLARE @score INT SET @score =  - IF @score >=  -    PRINT ' Pass ' ELSE    PRINT ' inferior lattice '

3. Case statements

The case statement is a multi-conditional branch statement, compared to the If ... Else statement, a case statement for branching process control can make the code clearer and easier to understand. The case statement determines the code flow that executes according to the true and false of the expression's logical value.

Grammar:

 Case input_expression        when  Then []      [ ] END

 Case      when  Then []      [ ] END

Example:

DECLARE @score INTSET @score =  -SELECT  Case @score              when  -  Then 'Full score'             when  -  Then 'Pass'        END         as 'Achievements'

DECLARE @score INTSET @score =  -SELECT  Case              when @score >=  -  Then 'Excellent'             when @score >=  the  Then 'Good'             when @score >=  -  Then 'Medium'             when @score >=  -  Then 'Pass'            ELSE 'inferior lattice'        END         as 'Achievements'

4. While statement

The while statement repeats one or more T-SQL code based on the condition and loops through the statement as long as the condition expression is true. You can use the break and CONTINUE keywords to control the execution of statements within a loop inside a while loop.

Grammar:

 while boolean_expression       | |  Break | CONTINUE

Parameters:

Boolean_expression: An expression that returns TRUE or FALSE. If the Boolean expression contains a SELECT statement, you must enclose the SELECT statement in parentheses.
{sql_statement | statement_block}:transact-sql statements or groups of statements defined with statement blocks. To define a statement block, use the control flow keyword BEGIN and END.
Break: Causes a while loop to exit from the most inner layer. Any statements that appear after the end keyword (the tag that ends the loop) are executed.
CONTINUE: Causes the while loop to start executing again, ignoring any statements that follow the CONTINUE keyword.

SQL Server Series: Process Control statements