Basic application of SQL Server control statements _mssql

1. Statement block (BEGIN ...) End)
The syntax for the statement block is as follows:
BEGIN
<sql statement or program block >
End
BEGIN ... End is used to set a statement block, you can encapsulate a number of Transact-SQL statements to form a block of statements, when processing, the entire statement block is treated as a statement. BEGIN ... End is often used in conditional statements, such as if ... ELSE or the while loop. BEGIN ... The end statement can be nested.

2. Judgment statement (IF ...) ELSE)
Usually the computer executes the statements in the program sequentially, but in many cases the order in which the statements are executed and whether the execution relies on intermediate results that depend on the running of the program, in which case it is necessary to determine which statements to execute or not to execute based on the value of a variable or expression. At this point, you can use the If ... Else statement to make a decision, choose to execute a statement or block of statements.
The syntax of the judgment statement is as follows:
IF < conditional expression >
< command line or statement block 1>
[ELSE [conditional expression]
< command line or statement block 2>]

3 detection statement (IF ...). EXISTS)
IF ... The EXISTS statement is used to detect whether the data exists, regardless of the number of rows that match it. For the existence of detection, the use of If ... exists is better than using count (*) >0, because the server stops executing the SELECT statement whenever the first matching row is found. The
Instrumentation statement syntax is as follows:
   if  [not]  exists  (select query statement)
        < command line or statement block 1>
   [ELSE]
   < command line or statement block 2>

4 Multiple branch judgment statements (case ...). When)
case ... When structure provides more than if ... Else structure more choice and judgment opportunities, use it can easily achieve multiple branch judgments, so as to avoid multiple if ... Else statement nested use. Multi-branch Judgment statement case ... When syntax is available in two formats:
The first format syntax is as follows:
     case < arithmetic expression
          when < arithmetic expression > THEN < op
         when < Arithmetic expressions > THEN < expressions
        [ELSE < arithmetic expressions]
     End
The second format syntax is as follows:
    case
        when < conditional expression > THEN < expression
        when < conditional expression > THEN < op > 
        [ELSE < expression]
    end

5 Loop statement (while)
A loop statement can set conditions that repeatedly execute a SQL statement or statement block, and repeat the execution of the statement if the specified condition is true (if the condition is valid).
The looping statement syntax is as follows:
While < conditional expression >
BEGIN
< command line or program block >
[Break]
[CONTINUE]
[Command line or program block]
End

6 Jump Statements (GOTO)
Using jump statement Goto can change the process of program execution, so that the program jumps to the specified program line marked with an identifier, and then continues to execute, the identifier as a jump target can be a combination of numbers and characters, but must end with ":".
The jump statement syntax is as follows:
GOTO identifier:

7 returns statement (return)
The return statement is used to end the execution of the current program to return to the previous program or other program that invoked it, specifying a return value within parentheses. The return statement causes the program to exit unconditionally from the batch, stored procedure, trigger, and no longer executes any statements after the returns. The return statement syntax is as follows:
return ([integer value])

8 Deferred execution statement (wait ... For) The
waitfor statement is used to temporarily stop program execution until the set waiting time has passed or the time set for it has arrived. Where the time must be datetime type data, delay time and time are in the "HH:MM:SS" format, the date cannot be specified in the WAITFOR statement, and the length of time cannot exceed 24 hours. The
deferred execution statement syntax is as follows:
     WAITFOR {DELAY < ' time ' > | Time < ' times ' >}
       sql_statement
      DELAY: Used to set the time interval for waiting, up to 24 hours.
      time: To set the point at which the wait ends.
      sql_statement: The SQL Action Statement to continue executing if the set wait time has passed or the time set is up.