Process Control statements in SQL Server

Process Control Statements

refers to the control of the logical Calculation section, which is used for program execution and process Fits.

1.Begin End Statement

Encapsulates multiple T-SQL statement combinations, which are composed of a unit to process. It is generally used in control flow statements, such as conditional queries or loops, to query for operations that meet certain conditions. Begin... End can be used in a nested set.

The syntax is as follows:

Example

2. Judgment Statement

The statement that executes when a certain condition is met is usually what we call the if... Else statement. Choose to execute a statement or block of statements by making a judgment

The syntax is as follows:

if< conditional Expression >--<sql statement or block >else< conditional expression >    --<sql statement or block >

Example

--declare is the meaning of the statement declare @money intselect @money =money from studentinfo where stuid = ' "If @money >20print ' too much money ' elsepr int ' Too little money '

where < conditional expression > can be a combination of various expressions, but must be true or false. else is optional, and the simplest if statement has no Else part

3. Detection Statements

if......exists statement to detect the existence of the data, of course, we can also detect the matching row count (*) to achieve, but no if......exists effect is good. Because if the first matching data is found, the server will stop detecting

Grammar rules

If [not]exists (select query statement) < command line or statement block >else < conditional expression >< command line or statement block >

Example  

--Check if student number 01 is present if exists (SELECT * from Studentinfo where stuid= ') print ' This student exists ' elseprint ' this student does not exist '

4. Multi-branch judgment statement

Case......when structures provide more choice and judgment than the if......else structure, and if you have learned the programming language, we should have seen this structure of the sentence. It makes it easy to implement multi-branch judgment, thus avoiding the use of if......else statement nesting. There are two types of grammar rules

Rule One

case< arithmetic Expressions >when< arithmetic expressions >then< operators >when< arithmetic expressions >then< operators >[else< arithmetic Expressions >]end

Rule Two

casewhen< arithmetic Expressions >then< operators >when< arithmetic expressions >then< operators >[else< arithmetic Expressions >]end

Example

5. Looping Statements

You can repeat the SQL statement or the block of statements that you want to execute, as long as the specified conditions are true

Break command to let the program completely jump out of the loop statement, end the while command,continue is to let the command return to execute

Grammar rules

While < conditional expression >begin<sql statement or program block >breakcontinue<sql statement or block >end

Example

--Calculate 1+2+3 ... 100 and declare @i int, @small intselect @i=1, @small =0while @i<=100   -Judging conditions beginset @small [email protected] [email Protected] Set @[email protected]+1continueendprint ' 1+2+3 ... 100 's and is ' print @small

6. Jump Statements

Use the goto statement to change the program's flow so that the program automatically jumps to the line of the program we want to execute

Syntax rules:

Goto identifiers:

Example

--Calculate 1+2+3 ... 100 and declare @i int, @small intselect @i=1, @small =0while @i<=100   -Judging conditions beginset @small [email protected] [email Protected] Set @[email protected]+1goto wodecontinueendprint ' 1+2+3 ... 100 and is ' Print @smallwode:p rint ' I jumped out '

Summary:

These statements when we are in the study of SQL SERVER will often encounter, in fact, it is not difficult, and we usually learn the programming language in the same control structure, but the format may be slightly modified, just try to find a few examples to practice a bit.