T-SQL variables

t-SQL variables

　　Types of variables:

In T-SQL, variables can be divided into global variables (Globals Variable) and local variables (local Variable) by their survival range

1, global variables are defined by the system, within the entire SQL Server instance can access the variable, all the variables start with @@ 开头 the user can only access, cannot be assigned value.

2. Local variables are defined by the user, and the life cycle is only valid within one batch. Local variables are defined and copied by the user, with @ as the first character.

Example:

DECLARE @i int    --Declare an int type local variable 　　 SET @i = Ten      　 --assigning values to local variables by set 　　 DECLARE @Name nvarchar( -)--Declare a nvarchar (20) type variable 　　 SET @Name = 'Zhang Fei' 　　 PRINT @ @VERSION     --all variables, read only, cannot be assigned 　　 SET @ @VERSION = '123'    --This line code error

　　Use of local variables:

• Record the number of loops in a loop
• Store the return value of a stored procedure or function

　　Declaration of a local variable:

The declaration of a local variable must be "DECLARE" as the keyword, and the name of the variable must be "@" as the first character of the variable name. You must provide a data type and data length for the declared variable.
Such as:

DECLARE @Name nvarchar ()

Note: The data type of the local variable cannot be text,ntext, and the image type, and the data length defaults to 1 when the data type is not provided for the character variable.

Set the value in the variable:

　　1. Set variables using Set

There are two ways to set the value of a variable. You can use the Selece statement or the SET statement. Functionally, they are almost identical, unlike the SELECT statement, which allows metadata values to come from a column in a SELECT statement.

DECLARE @i　　int,@jintset@i=ten;　　 set@j=;  Select@i+@j　　

To set a variable with a queried value:

DECLARE @i int SET @i = (SelectMAX from person ) --When you use set, you will get an error if you return multiple rows of results. Also, if you return more than one record 　　 , SELECT@i　　

　　2. Use Select to set the variable:

When the information stored in a variable originates from a query, it is often used to assign a value to a variable using SELECT, which is easier to syntax.

DECLARE @i int SELECT @i =  - SELECT @i　　　　

The information for the query is assigned to the variable:

DECLARE @i int SELECT @i =  from Order  by desc    --use last assignment when multiple values are returned PRINT @i

Select sets multiple values at the same time:

DECLARE @Name varchar () DECLARE @i int SELECT @i = Ten @Name = ' Zhang Fei '　　

　　3. When an expression does not return a value

• When a local variable is assigned a value using set, if the assignment expression does not return a value, the local variable becomes null, and when the select assigns a value to the expression, the local variable remains the original value if the expression does not return a value.
• Everything only declares that the initial value of a local variable that does not have an assignment is "NULL".

For example:

DECLARE @Name nvarchar( -)SET @Name = 'Wong Fei Hung' 　　 Select @Name =Name fromPerson_1whereId=  -    --100 does not exist, the value of the @name variable is changed if it is changed to an ID that exists and the name column is not a null value. 　　 PRINT @Name--still printing is the yellow Feihong

It is visible that when the select expression is assigned a value, the original value is maintained if it is a return value.

　　Local Table variables:

A local table variable is a special local variable. Unlike temporary tables, local table variables have all the characteristics of local variables. In queries, because local table variables are present in memory, not on the hard disk, the speed is much faster than the temporary table or the actual table, where the local table variable is used most when it serves as the intermediate table for multiple tables in the query.

DECLARE @TempTable Table --Declares a local table variable (Idint, Namenvarchar( -))INSERT  into @TempTable--Inserting a query into a local table variable as dataSELECTId,name fromperson_1SELECT *  from @TempTable    --As with ordinary tables, you can join, sub-query and so on. 

T-SQL variables