T-SQL query advanced-flow control statements

Overview

Like other advanced languages, the T-SQL also has statements used to control the flow. The flow control statement in the T-SQL further extends the power of the T-SQL ...... This allows most of the business logic to be performed at the database level. However, many people do not have a systematic understanding of the process control statements in T-SQL.ArticleSystem to explain the flow control statement in the T-SQL statement.

 

Basic Concepts

In the absence of flow control statements, T-SQL statements are executed one by one in the order from top to bottom:

The process control statement allows developers to perform selective jump based on certain logic, and implements a jump structure similar to advanced languages:

 

Scope of use of flow control statements and go keywords

Flow Control statements can only be used in a single batch segment (batch), user-defined functions and stored procedures. You cannot span multiple batch processing segments or customize functions and stored procedures.

Because the focus here is on the T-SQL query statement, so here only talk about the batch processing segment (batch ).

A batch segment is a batch of one or more statements. It is called batch processing because all statements are submitted to one SQL instance at a time. In this batch processing range, local variables are mutually visible.

To submit multiple statements to an SQL instance multiple times, you must useGoKeyword.GoThe keyword itself is not an SQL statement,GoThe keyword can be considered as an identifier of the end of the batch processing. When the go keyword is encountered, the current statement before go will be directly transferred to the SQL instance for execution as a batch processing. Therefore, local variables are not visible in the same batch, and process control statements cannot be used for cross-Batch statements.

In the same batch processing, local variables are mutually visible:

 

Local variables are invisible in different batches:

 

In different batches, process control statements cannot be processed across batches:

8 flow control statement keywords in T-SQL

In T-SQL, there are eight keywords related to the flow control statement:

 

Begin... end	Break
Goto	Continue
If... else	While
Return	Waitfor

Next, I will explain the above keywords one by one.

 

 

Begin... End keyword

Begin... The end keyword is also the most basic keyword used by flow control statements. It is used to divide multiple statements into logical parts. In fact, it can be directly understood as curly braces ("{}") in C-like languages.

 

While/break/continue keywords

In the flow control statement of the T-SQL, the loop statement only has the while loop, and does not have the for and switch loop of the traditional advanced language. In addition to the loop of flow control statements, while is often used in cursors.

The while keyword is almost identical to the while keyword in the advanced language. In a while loop, you can use the break and continue keywords to control the loop.

The continue keyword ends this loop and starts the next loop directly.

The break keyword is used to directly jump out of the while loop statement.

It is worth noting that when the while loop is nested, The continue keywords and break keywords only apply to the while loop in which they reside, and do not apply to the external while loop.

A simple example is as follows: from 1 to 10, when the cycle is 7, the cycle ends and continues. When the cycle is 8, the cycle jumps out.

If. Else keyword

The IF... else keyword implements the logic of the other. This is exactly the same as if... else in advanced languages. I will not talk about it here. For example, refer.

Note that if is often combined with the exists keyword to check whether the specified data exists in the data table. For example:

I want to query the persons without superiors in the employee. If there is such a person, the output is "XXX is our boss". If not, the output is "there is no infomation about our boss"

 

Goto keywords

The goto keyword can be disrupted.ProgramIn advanced languages. The use of the goto keyword is very simple. You can define a jump tag as long as the Goto tag name is used. If you must use the goto keyword, the best practice is to use it only for error handling. For example:

 

Return keyword

Return is the simplest, most effective, and most effective way to tell the server to jump out of a batch segment (batch), user-defined functions and stored procedures. In the same batch processing, the return keyword ends directly with the batch processing (batch) where the current return is located. For batch processing concepts, see the previous go keyword section.

The simple return concept is as follows:

In the stored procedure, a number can be returned after the return statement to return the execution status or error.Code.

Many people will confuse the return statement with The raiserror function. The difference is that the raiserror function will cause an error and the program will continue to execute:

Waitfor keyword

The waitfor keyword allows you to specify the statement to be executed at a specific time or delay a specific time.

The syntax for deferred wait and execution at a specific time is waitfor delay 'execution time to wait ', waitfor time' precise time to execute the program', respectively'

A simple syntax example is as follows:

The waitfor function can implement more complex business logic, such:

I want to start a promotion. It will start after 10 hours, which will be off. It will end after 20 hours and change to off:

 

Summary

This article explains 8 Keywords of flow control statement from the perspective of T-SQL query. Making good use of these keywords is essential for mastering complex T-SQL queries.