T-SQL query Advanced: variable

Overview

Variables are an essential part of a language, and of course, it is the same for T-SQL. In simple queries, variables are rarely used, but variables are an essential part of a complex query or stored procedure.

Types of variables

In T-SQL, a variable can be divided into global variables (globally Variable) and locals (local Variable) by their scope of survival.

1. Global variables are system-defined variables that can be accessed throughout an instance of SQL Server. Global variables with @@ 作为 The first character, the user can only access and cannot assign a value.

2. Local variables are defined by the user, and the lifecycle is valid only within one batch. Local variables are defined and assigned by the user themselves with @ as the first character.

A simple example is as follows

Because the global variable is only used to read some of the parameters of the system, the specific meaning of each global variable please Google ... This article mainly introduces local variables (that is, user-defined variables).

Use of local variables

In T-SQL, a local variable is an object that stores a single value for the specified data type. The definition of variables in T-SQL is actually the same as in most high-level languages.

Local variables are often used in the following three uses:

1. The number of times a loop is recorded in a circular statement, or the number of times it is used to control loops.

2. Used to store process statements to control flow direction