MS SQL Basics Tutorial: SQL variables

There are two types of variables that can be used in Transact-SQL, one being a local variable (Variable) and the other being a global variable Variable.

4.4.1 Local Variables

A local variable is a user-customizable variable that is scoped to the program. It is commonly used in programs to store data that is queried from a table or used as a staging variable during program execution. The local variable must begin with "@" and must be used before the declare command is described. The following forms are described as follows:

DECLARE @ variable name variable type [@ variable name variable type ...]

The variable type can be all data types supported by SQL Server 2000, or it can be a user-defined data type. For a detailed use of the DECLARE command, see "4.6 Other commands."

In Transact-SQL, you cannot use the variable = variable value to assign a value to a variable as you would in a normal programming language. You must use the Select or SET command to set the value of the variable, whose syntax is as follows:

select@ local variable = variable value

SET @ local variable = variable value

Example 4-5: Declare a variable "id" of a length of 10 characters and assign a value

Declare@id Char (10)

Select@id= ' 10010001 '

Note: You can assign a column value directly to a variable in the SELECT command when querying data in the Select command. Example 4-6: The employee and the payroll with the query number "10010001" assigned to the variable name and wage respectively.

Example 4-6

Use Pangu

Declare@name Char @wage Money

Select@name=e_name, @wage =e_wage.

From employee

where emp_id= ' 10010001 '

Select@name as E_name, @wage as E_wage

The results of the operation are as follows:

E_name E_wage

------------------------------ -----------

John 8000.0000

Note: There are keywords in the database language and programming language, keywords are combinations of characters that can cause an action to occur in a certain way, avoid the use of keywords in command tables, columns, variables, and other objects, to avoid conflicts and generate errors. For SQL Server keywords, see the Appendix.

4.4.2 Global Variables

Global variables are variables used internally within the SQL Server system and are not scoped to a program, but any program can call a global variable at any time, usually storing configuration settings and performance statistics for some SQL Server. Users can test the system's settings or state values after Transact-SQL commands in a program with a global variable. For more information about SQL Server Global variables, see the Appendix.

Note: Global variables are not defined by the user's program, they are predefined at the server level. Only predefined and defined variable variables can be used. When referring to global variables, you must start with "@@". The name of a local variable cannot be the same as the name of a global variable, otherwise an error occurs in the application.

See the full set of "MS SQL Basics Tutorials"