SQL Server Programming (01) Process Control

A set of commands that the application sends to SQL Server, which is compiled into an executable unit called an execution plan, executed one at a time by the statements in the execution plan.

Each different batch is split with the GO command. The GO command is not an SQL statement, it tells SSMs to send the statement before go to the database engine as a batch.

Note: The go command cannot be written on the same line as other SQL commands! The scope of the local variable is limited to a batch, can not be referenced after go, otherwise error!

In addition, when multiple batches are committed at one time, one batch fails, affecting only its own execution, without affecting the execution of the next batch process.

Statement block

Use begin in T-SQL ... End to specify the statement block.

Note: Variables declared in a statement block are scoped to the entire batch, that is, at begin ... The variables defined in end can be accessed outside of the end until you meet go.

BEGIN    Declare @str nvarchar( -)    Set @str='ABC'    Print(@str)ENDPrint(@str)--normal access is available hereGoPrint(@str)--error here: the scalar variable "@str" must be declared. 

Conditional statements

IF ... ELSE ... Statement

The following code is purely illustrative:

Declare @num intSet @num=3if(@num=1)    begin        --you can write multiple lines of code here.        --if it is a row, you can omit Begin...end        Print 'Num=1'    EndElse if(@num=2)    --There is only one line of code here, omit Begin...end    Print 'num=2'Else    Print 'num is not equal to 1, nor is it equal to 2'

Case...when...then...end statements

Seems to be used only in SELECT statements? Two ways to use:

The first type:

Declare @num intSet @num=3Select      Case @num         when 1  Then 'Num=1'         when 2  Then 'num=2'        Else 'num is not equal to 1, nor is it equal to 2'    End  asAo.

The second type:

Declare @num intSet @num=3Select      Case    --The expression is not written after the case , and the expression is written behind the When         when @num=1  Then 'Num=1'         when @num=2  Then 'num=2'        Else 'num is not equal to 1, nor is it equal to 2'    End  asCol

Seems to be used only in SELECT statements? I tried to get Case...when...then...end out of SELECT, but it didn't work ...

Looping statements

While loop

Let's go straight to a simple example:

Declare @num intSet @num=3 while(@num>0)--parentheses don't seem to be necessary either.    begin        Print @num        Set @num=@num-1    EndGo

Implementing loops using Goto statements

Goto statement is actually used to jump, it is said that the use of Goto will make the code more chaotic than pasta, so many programming languages in the Goto basic is not how to use.

In T-SQL, we can use Goto to implement loop processing with the following code:

Declare @num intSet @num=3lb:--Mark, the name can start on its ownPrint @numif(@num>1)begin    Set @num-=1; GotolbEnd

SQL Server Programming (01) Process Control