Shulou(Shulou.com)05/31 Report--

This article mainly explains the "what SQLServer variables related knowledge", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "what SQLServer variables related knowledge" bar!

1. Overview

The SQLServer variable corresponds to a storage space in memory. Unlike a constant, the value of a variable can be changed during execution.

2. Classification

According to the different scope of action, SQLServer variables are mainly divided into local variables and global variables.

2.1. Local variable

A local variable is a variable defined by a user in a program, and it is valid only within the scope of the defined program. Local variables can be used to hold the data read from the table, or as temporary variables to save the intermediate results of the calculation.

Note: the first character of a local variable name must be an @.

Example:

-- define two local variables DECLARE @ m int,@n int;-- assign SET @ m _ variables; SET @ n _ variables;-- query SELECT @ m _ variables

2.2 Global variables

Global variables usually start with @ @. These variables are generally system functions in SQL Server, and their syntax follows the rules of the function. Users can use these functions in the program to test the system characteristics and the execution of SQL commands. Common global variables are as follows:

@ @ ERROR-- the error number of the last SQL error @ @ IDENTITY-- the last inserted identification value @ @ LANGUAGE-- the name of the language currently in use @ MAX_CONNECTIONS-- the maximum number of simultaneous connections that can be created @ @ ROWCOUNT-- the number of lines affected by the last SQL statement @ @ SERVERNAME-- local server information @ @ TRANSCOUNT-- current connection call. Number of things opened @ @ VERSION-- check the version information of SQL Server 3. Declare variable

3.1. Simple statement

Variables can only be used after they have been declared, and you can use the declare statement to declare variables. You can specify the data type and length of the variable when you declare the variable.

DECLARE @ name nvarchar (30)

3.2 declare multiple variables

To declare multiple local variables, you need to follow a comma after the defined local variable, and then specify the next local variable name and data type.

DECLARE @ Name nvarchar (30), @ Age int

3.3 variable scope

When using variables, you need to pay attention to the scope of the variables. Variables have local scope and are visible only in the batch or process in which they are defined. The scope ranges from where the variable is declared to the end of the batch or stored procedure that declares the variable.

USE DB; GO DECLARE @ Age int; SET @ Age = 30; GO-- Terminator select @ Age-- error 4. Variable assignment

After the variable is declared, the value of the variable is set to NULL by default. You can use SET or SELECT statements. SET is the preferred method for assigning values to variables.

Variables can also be assigned by selecting the value currently referenced in the list. If a variable is referenced in the selection list, it should be assigned a scalar value or the SELECT statement should return only one row.

Example:

DECLARE @ Age int;-- SELECT statement assignment SELECT @ Age = age from t_user ORDER BY age desc;-- returns the value of the last line select @ Age; Thank you for reading, this is the content of "what knowledge about SQLServer variables". After the study of this article, I believe you have a deeper understanding of what SQLServer variables are related to, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.