SQL Server variable Assignment introduction description

Variables are an essential part of a language, and of course, the same is true for T-SQL. In simple queries, variables are often seldom used,

However, variables are an essential part of a complex query or stored procedure. In SQL Server, variables are divided into global variables and local variables in two ways:

One. Global variables:

A global variable is a system-defined variable that can be accessed throughout an instance of SQL Server. The global variable begins with two @@ 符号, and the user can only access and not assign a value.

SQL Server provides altogether more than 30 global variables

Common:
@ @IDENTITY: Returns the column value of the last inserted row's identity column.
@ @ERROR: Returns the error code for the last Transact-SQL statement executed. Zero if there is no error.
@ @ROWCOUNT: Returns the number of rows affected by the previous statement, and any statement that does not return a row sets this variable to 0.
@ @DBTS: Returning the timestamp value of the current database must ensure that the value of the timestamp in the database is unique.
@ @SERVERNAME: Returns the name of the local server running SQL Server 2000.
@ @REMSERVER: Returns the name of the remote SQL Server server as recorded in the login record.
@ @CONNECTIONS: Returns the number of connections or attempts to connect since the last time SQL Server was started, allowing administrators to easily understand the number of attempts to connect to the server today.
@ @CURSOR_ROWS: Returns the number of qualifying rows that currently exist in the cursor that was last connected and opened.
@ @VERSION: Returns the date, version, and processor type of the current installation of SQL Server.
@ @CPU_BUSY: Returns the CPU's working time since the most recent startup of SQL Server in milliseconds.
@ @DATEFIRST: Returns the value of the Datafirst parameter that is assigned using the SET DATEFIRST command. The SET datefirst command is used to specify the day of the week for the first days of the week.
@ @FETCH_STATUS: Returns the status value of the last FETCH statement.
@ @IDLE: Returns the length of time, in milliseconds, that the CPU has been in a closed state since the most recent SQL Server startup.
@ @IO_BUSY: Returns the amount of time (in milliseconds) that the CPU took to perform an input-output operation since SQL Server was last started.
@ @LANGID: Returns the currently used language ID value.
@ @LANGUAGE: Returns the name of the language currently in use.
@ @LOCK_TIMEOUT: Returns the length of time that the current session waits for a lock in milliseconds.
@ @MAX_CONNECTIONS: Returns the maximum number of connections allowed to connect to SQL Server.
@ @MAX_PRECISION: Returns the precision of the decimal and numeric data types.
@ @NESTLEVEL: Returns the nested progression of the currently executing stored procedure with an initial value of 0.
@ @OPTIONS: Returns information for the current SET option.
@ @PACK_RECEIVED: Returns the number of input packets read by SQL Server over the network.
@ @PACK_SENT: Returns the number of output packets that SQL Server writes to the network.
@ @PACKET_ERRORS: Returns the number of network packet errors.
@ @PROCID: Returns the ID value of the current stored procedure.
@ @SERVICENAME: Returns the status of the service that SQL Server is running under: such as MS SQL Server, MSDTC, SQLServerAgent.
@ @SPID: Returns the server processing ID value processed by the current user.
@ @TEXTSIZE: Returns the TEXTSIZE option value of the SET statement the SET statement defines the text or image in the SELECT statement. The maximum length of the data type base unit is bytes.
@ @TIMETICKS: Returns the number of microseconds per 1:.
@ @TOTAL_ERRORS: Returns the number of disk read and write errors.
@ @TOTAL_READ: Returns the number of disk read operations.
@ @TOTAL_WRITE: Returns the number of disk write operations.
@ @TRANCOUNT: Returns the number of transactions in the active state of the current connection.

Two. Local variables

Local variables are defined by the user, and the life cycle is only valid within one batch. Local variables begin with an @ sign and are defined and assigned by the user. Use Select or set when assigning values.

Example:

DECLARE @age int

DECLARE @name varchar (20)

Set @name = ' Zhang San '--assigning variables with Set method Note: This method assigns only one variable at a time

Select @age =age from client where [name][email protected]-query customer Zhang San age assignment to @age variable Note: This method can assign values to multiple variables at once

Original address: http://bbs.delit.cn/thread-196-1-1.html

Reprint please specify the source:

Author: metric Technology http://www.delit.cn

SQL Server variable Assignment introduction description