SQL Process Control command

The process control commands used in Transact-SQL languages are similar to the common programming languages, with the following control commands.

4.6.1 IF ... ELSE
The syntax is as follows:
IF < conditional expression >
< command line or program block >
[ELSE [conditional expression]
< command line or program block
where < conditional expression > can be a combination of various expressions, the value of an expression must be the logical value TRUE or FALSE. The ELSE clause is optional, and the simplest if statement has no ELSE clause part. IF ... else is used to determine the execution of a procedure when a condition is set up and to execute another procedure when the condition is not established. If you do not use a program block, if or else you can only execute one command. IF ... else can be nested.

Example 4-9
declare@x int,@y Int,@z int
Select @x=1,@y=2, @z=3
If@x>@y
print ' X>y '--printing string ' x>y '
else if@y>@z
print ' Y>z '
else print ' z>y '
The results of the operation are as follows
Z>y
Note:Up to 32 levels can be nested in Transact-SQL.

4.6.2 BEGIN ... End
The syntax is as follows:
BEGIN
< command line or program block >
End
BEGIN ... End is used to set a program block that will be in the begin ... End of all programs as a unit execution begin ... End often in conditional statements, such as if ... else in use. At the begin ... End can be nested in another begin ... End to define another program block.

4.6.3 case
The case command has two statement formats:
Case < expression >
When < expressions >THEN< expressions >
...
when< Expressions >THEN< Expressions >
[else< expression;
End

Case
When < conditional expression > THEN < op >
When < conditional expression > THEN < op >
[ELSE < expression]
End
The case command can be nested into the SQL command.
Example 4-10: Adjust employee pay, work level of "1" increase 8%, work level of "2" up 7%, work level of "3" up 6%, others up 5%.
Use Pangu
Update employee
Set e_wage =
Case
When job_level = ' 1 ' then e_wage*1.08
When job_level = ' 2 ' then e_wage*1.07
When job_level = ' 3 ' then e_wage*1.06
else e_wage*1.05
End
Note: When the case clause is executed, only the first matching child name is run.

4.6.4 while ... CONTINUE ... Break
The syntax is as follows:
While < conditional expression >
BEGIN
< command line or program block >
[Break]
[CONTINUE]
[Command line or program block]
End
The while command repeats the command line or program block when the set condition is established. The Continue command lets the program skip the statement after the continue command and back to the first line of the while loop. The break command lets the program completely jump out of the loop, ending the execution of the while command. A while statement can also be nested.

Example 4-11:
DECLARE @x int @y int @c int
Example 4-11:
declare @x int, @y int, @c int
Select @x = 1, @y=1
While @x < 3
Begin
Print @x--Prints the value of the variable x
While @y < 3
Begin
Select @c = 100*@ x+ @y
Print @c--printing the value of variable C
Select @y = @y + 1
End
Select @x = @x + 1
Select @y = 1
End
The results of the operation are as follows
1
101
102
2
201
202

4.6.5 WAITFOR
The syntax is as follows:
WAITFOR {DELAY < ' time ' > | Time < ' times ' >
| Errorexit | ProcessExit | Mirrorexit}
The WAITFOR command is used to temporarily stop program execution until the set waiting time has passed or the time set for it has arrived before proceeding. Where ' time ' must be a datetime type of data, such as: ' 11:15:27 ',
But cannot include the date the keyword meaning is as follows:

• DELAY is used to set the waiting time up to 24 hours;
• Time is used to set the point at which the wait ends;
• Errorexit until the abnormal interruption is handled;
• ProcessExit until the normal or abnormal interruption is handled;
• Mirrorexit until the mirror device fails.

Example 4-12 waits 1 hours 2 minutes 3 seconds before the SELECT statement is executed
WAITFOR DELAY ' 01:02:03 '
SELECT * FROM Employee

Example 4-13: Wait until the evening 11 o'clock 8 minutes before the SELECT statement is executed
waitfor time ' 23:08:00 '
SELECT * FROM Employee

4.6.6 GOTO
The syntax is as follows:
GOTO identifier
The GOTO command is used to change the flow of a program's execution, so that the program jumps to the specified program line marked with an identifier and continues to execute. An identifier that is a jump target can be a combination of numbers and characters, but must end with ":", such as ' 12: ' or ' a_1: '. On the goto command line, the identifier does not have to be followed by ":".
Example 4-14 branches print characters ' 1 ', ' 2 ', ' 3 ', ' 4 ', ' 5 '
DECLARE @x int
Select @x = 1
Label_1
Print @x
Select @x = @x + 1
While @x < 6
Goto Label_1

4.6.7 return
The syntax is as follows
return [integer value]
The return command ends the execution of the current program and returns to the previous program or other program that invoked it. You can specify a return value within parentheses.
Example 4-15
DECLARE @x int @y int
Select @x = 1 @y = 2
If X>y
Return 1
Else
Return 2
If you do not specify a return value, the SQL Server system returns a default value based on the results of the program execution, such as
shown in table 4-5.


If the run process causes multiple errors, the SQL Server system returns the value that is the maximum value, and returns the user-defined value if the user defines the return value at this time. The return statement cannot returns a null value.