Basic Application of SQL Server Control statements

1. Statement block (BEGIN... END)
Statement block Syntax:
BEGIN
<SQL statement or program block>
END
BEGIN... END is used to set a statement block. Multiple Transact-SQL statements can be encapsulated to form a statement block. during processing, the entire statement block is considered as a statement. BEGIN... END is often used in conditional statements, such as IF... ELSE Or WHILE loop. BEGIN... END statements can be nested.

2. Judgment Statement (IF... ELSE)
Generally, the computer executes the statements in the program in sequence, but in many cases, the execution order of the statements and whether the execution depends on the intermediate results of the program running. In this case, you must make a judgment based on the value of a variable or expression to determine which statements are executed or which statements are not executed. In this case, IF... The ELSE statement determines whether to execute a statement or statement block.
The statement syntax is as follows:
IF <conditional expression>
<Command line or statement Block 1>
[ELSE [conditional expression]
<Command line or statement Block 2>]

3 check Statement (IF... EXISTS)
IF... The EXISTS statement is used to check whether data EXISTS without considering the number of matched rows. For existence detection, IF... EXISTS is better than using COUNT (*)> 0 and more efficient, because the server stops executing the SELECT statement as long as the first matching row is found.
The syntax of the check statement is as follows:
IF [NOT] EXISTS (SELECT query statement)
<Command line or statement Block 1>
[ELSE]
<Command line or statement Block 2>

4 multi-branch judgment statements (CASE... WHEN)
CASE... The WHEN structure provides... The ELSE structure provides more options and opportunities for judgment. It can be used to easily implement multi-branch judgment to avoid multiple IF... Use nested ELSE statements. Multi-Branch judgment statement CASE... The WHEN syntax has two formats:
The first format syntax is as follows:
CASE <arithmetic expression>
WHEN <arithmetic expression> THEN <arithmetic expression>
WHEN <arithmetic expression> THEN <arithmetic expression>
[ELSE <arithmetic expression>]
END
The second format syntax is as follows:
CASE
WHEN <conditional expression> THEN <formula>
WHEN <conditional expression> THEN <formula>
[ELSE <formula>]
END

5. Loop Statement (WHILE)
Loop statements can be used to set the conditions for repeated SQL statements or statement blocks. If the specified condition is TRUE (the condition is TRUE), the statement is executed repeatedly.
The statement syntax is as follows:
WHILE <conditional expression>
BEGIN
<Command line or program block>
[BREAK]
[CONTINUE]
[Command line or program block]
END

6. Jump Statement (GOTO)
The jump statement GOTO can be used to 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, as the jump target identifier can be a combination of numbers and characters, but it must end.
The syntax of the jump statement is as follows:
GOTO identifier:

7. RETURN Statement)
The return statement is used to end the execution of the current program and return to the program or other programs that call it. A return value can be specified in parentheses. RETURN statements enable the program to exit unconditionally from the batch processing, stored procedures, and triggers without executing any statements after RETURN. The returned statement syntax is as follows:
RETURN ([integer])

8. deferred execution statement (WAIT... FOR)
The WAITFOR statement is used to temporarily stop program execution until the set wait time has elapsed or the set time has reached. The time must be of the DATETIME type. The delay time and time are in the "HH: MM: SS" format. In the WAITFOR statement, the date cannot be specified and the length cannot exceed 24 hours.
The syntax of the deferred execution statement is as follows:
WAITFOR {DELAY <'time'> | TIME <'time'>}
SQL _statement
DELAY: used to set the wait interval, which can be up to 24 hours.
TIME: used to set the waiting TIME.
SQL _statement: the SQL operation statement to continue execution if the specified wait time has elapsed or the specified time has reached.