Declaration and assignment of local variables and global variables in SQL Server

A local variable is identified with a @ identifier, with a global variable of two @ (commonly used global variables are generally defined);

Declare local variable syntax: DECLARE @ variable name data type; For example: declare @num int;

Assignment: There are two methods (@num for variable names and value for values)

Set @num =value; or select @num =value;

If you want to get a field value from a query statement, you can assign a value to a variable with SELECT, as follows:

Select @num = field name from table name where ...

In the MySQL tutorial, variables need not be stated in advance, and use the "@ variable name" directly when using.

The first usage: set @num = 1; or set @num: = 1; To use variables to save data, use @num variables directly

Second usage: select @num: = 1; or select @num: = field name from table name where ...

Note the above two assignment symbols, which can be used with "=" or ": =" when using set, but must use ": = Assignment" When using Select

In SQL, we often use temporary tables to store temporary results. This is useful when the result is a collection, but when the result is just a data or a few data, it is more cumbersome to build a table, and when certain elements in an SQL statement change frequently, such as selecting a condition , (at least I think) should use local variables. Of course, the global variables for MS SQL Server are also useful.

> > > > Local variables

Statement: DECLARE @local_variable data_type

@local_variable is the name of the variable. Variable names must begin with an at character (@). Data_type is any system-supplied or user-defined data type. A variable cannot be a text, ntext, or image data type.

Example:

Use master
Declare @sel_type char (2)
DECLARE @sel_cunt numeric (10)
Set @sel_type = ' u '/*user table*/
Set @sel_cunt = 10

/* Returns the number of user tables in the system * *
Select @sel_cunt = COUNT (*)
From sysobjects
where type = @sel_type

Select @sel_cunt as "User table" ' s count '

If you want to return the number of system tables, you can use set @sel_type = ' s '

Perhaps this example does not explain the benefits of using variables, I just want to explain how to use them. When a group (several or even dozens of) of SQL statements use a variable, it is possible to realize his benefits.

> > > > Global variables

Global variables are system-predefined, returning some system information, with global variables beginning with two at (@). Here are some of the more common variables I've counted.

@ @connections
Returns the number of connections or attempts to connect since the last startup.

@ @cursor_rows
Returns the number of qualifying rows currently present in the last open cursor on the connection.

@ @datefirst
Returns the number of the first day of the week

@ @error
Returns the error code for the last executed SQL statement.

@ @fetch_status
Returns the state of the last cursor executed by the FETCH statement, not the state of any cursors currently opened by the connection.

@ @identity
Returns the last-inserted identity value

@ @langid
Returns the local language identifier (ID) of the language currently in use.

@ @language
Returns the currently used language name.

@ @lock_timeout
Returns the current lock timeout setting for the current session in milliseconds.

@ @procid
Returns the stored procedure identifier (ID) of the current procedure.

@ @rowcount
Returns the number of rows affected by the previous statement.

@ @servername
Returns the name of the local server that is running.

@ @spid
Returns the server process identifier (ID) of the current user process.

@ @trancount
Returns the number of active transactions for the current connection.

@ @version
Returns the date, version, and processor type of the current installation.