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.