Declaration and assignment of local variables and global variables in sqlserver

A local variable is identified with one @, and a global variable is identified with two @ (common global variables are usually defined). declare the local variable Syntax: declare @ variable name data type. For example: declare @ numint; value assignment: There are two methods (@ num is the variable name, value is the value) set @ numvalue; & nbsp; or & nbsp; select @ numvalue; if you want to obtain a local variable in the query statement, use @ to identify the global variable, and use @ (common global variables are usually defined );

Declare the local variable Syntax: declare @ variable name data type; for example, declare @ num int;

Value assignment: There are two methods (@ num is the variable name and value is the value)

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

If you want to obtain a field value in the query statement, you can use select to assign values to the variable, as shown below:

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

You do not need to declare the variable beforehand. You can simply use "@ variable name" when using it.

First usage: set @ num = 1; or set @ num: = 1; // use the variable to save the data. Use the @ num variable directly.

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

Note that the above two values can be set with "=" or ": =", but ": =" must be used for select"

In SQL, we often use temporary tables to store temporary results. This method is very useful when the results are a set, but when the results are only one or several pieces of data, creating a table is troublesome. In addition, when some elements in an SQL statement change frequently, such as selection conditions, (at least I want to) local variables should be used. Of course, the global variables of ms SQL server are also very useful.

>>>> Local variables

Declaration: declare @ local_variable data_type

@ Local_variable is the variable name. The variable name must start with the at sign. Data_type is any data type provided by the system or defined by the user. The variable cannot be of the 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

/* Return 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'

This example may not illustrate the advantages of using variables. I just want to explain how to use them. When a group of (several or even dozens) SQL statements use a certain variable, they can realize its advantages.

>>>> Global variables

Global variables are predefined by the system. Some system information is returned. The global variables start with two. Below are some common variables I have collected.

@ Connections
Returns the number of connections or attempts since the last start.

@ Cursor_rows
Returns the number of qualified rows in the cursor that is last opened on the connection.

@ Datefirst
Returns the number of the first day of each week.

@ Error
Returns the error code of the last executed SQL statement.

@ Fetch_status
Returns the status of the last cursor executed by the fetch statement, rather than the status of any cursor currently opened.

@ Identity
Returns the last inserted id value.

@ Langid
Returns the local language identifier (id) of the current language ).

@ Language
Returns the current language name.

@ Lock_timeout
Returns the current lock timeout settings for the current session, in milliseconds.

@ Procid
The identifier (id) of the current process ).

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

@ Servername
Returns the name of the running local server.

@ Spid
Returns the id of the server process 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.