Process Control statements in SQL Server

Process Control Statements

refers to the command that is used to control program operation and process Fits. Generally refers to the control of the logical calculation section.

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:

Sample Example

2. Inference Statements

Statements that run when a certain condition is met are usually what we call the if... Else statement. By making inferences. Choose to run a statement or statement block

The syntax is as follows:

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

Sample 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 '

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

3. Test Statements

if......exists statement is used to detect the existence of data, of course, we can also be detected by the matching row count (*) to achieve, but no if......exists effect is good. Because the assumption is only to find the first matching data. The server will stop checking

Grammar rules

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

Sample 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 inference statements

Case......when The structure provides many other options and inference opportunities than the IF......ELSE structure. Suppose that students who have studied programming languages. We should have seen a statement of this structure. It is very convenient to implement multi-branch inference, which avoids 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

Sample Example

5. Looping Statements

The ability to run SQL statements repeatedly or block of statements to run. Just specify the condition as soon as possible

Break command to let the program jump out of the Loop statement and end the while command. continue is to let the command continue back to run

Grammar rules

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

Sample Example

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

6. Jump Statements

Using the goto statement can change the process of the program, allowing the program to jump to the program line that we want to run

Syntax rules:

Goto identifiers:

Sample Example

--Calculate 1+2+3 ... 100 and declare @i int, @small intselect @i=1, @small =0while @i<=100   -inferred 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 are often encountered in our learning of SQL SERVER , and in fact it is not difficult, as is the control structure in our usual learning programming language, just a slight change in format. Just try it and find a few examples to practice.

Process Control statements in SQL Server