Use and description of SQL Server local variables

Using declare
The following example uses a local variable named @find to retrieve all the author information that begins with the ring of the last name.
Copy code code as follows:
Use pubs
DECLARE @find varchar (30)
Set @find = ' ring% '
Select Au_lname,au_fname,phone
From authors
where au_lname like @find

@find is a local variable.

B. Use of two variables in declare
The following example retrieves the name of the employee employed since January 1, 1993 from the employee of Binnet & Hardley (pub_id = 0877).
Copy code code as follows:
Use pubs
SET NOCOUNT ON
Go
Declare @pub_id char (4), @hire_date datetime
Set @pub_id = ' 0877 '
Set @hire_date = ' 1/01/93 '
--The SELECT statement syntax to assign values to two local
--variables.
--Select @pub_id = ' 0877 ', @hire_date = ' 1/01/93 '
SET NOCOUNT OFF
Select FName, lname
From employee
where pub_id = @pub_id and hire_date >= @hire_date

Here is the result set:

FName lname
-------------------- ------------------------------
Anabela Domingues
Paul Henriot

(2 row (s) affected)

Local variables
A local variable is a user-customizable variable that is scoped to the program. The name of the local variable is user-defined, and the named local variable name conforms to the SQL Server 2000 identifier naming convention, and the local variable name must begin with the @.
1. Declaring local variables
The declaration of a local variable requires the use of a Declare statement.
Grammar:
Declare
{
@varaible_name datatype [,... n]
}
Parameter description:
L @varaible_name: The variable name of a local variable must begin with a @, and the name of the variable must conform to the name of the SQL Server identifier.
L DataType: The data type used by local variables can be all system data types and user custom data types except text, ntext, or image types. In general, if there is no special purpose, it is recommended to use the data type provided by the system as much as possible when applying. Doing so reduces the effort to maintain your application.
For example, declare a local variable @ songname.
The SQL statement is as follows:
Declare @songname char (10)
2. Assigning values to local variables
There are generally two ways to assign a value to a variable, either by using a SELECT statement or by using a set statement. The syntax for assigning values to variables using a SELECT statement is as follows:
Select @varible_name = Expression
[From table_name [,... N]
WHERE clause]
The SELECT statement above is intended to assign a value to a variable, rather than to query the data from the table. Also, you do not have to use the FROM keyword and the WHERE clause in the process of assigning values using the SELECT statement.
Example:
In the "course" Table of the "Student" Database tutorial, "course content" is the "art class" information assigned to the local variable @songname, and its value is displayed with the Print keyword. The results run in Query Analyzer are shown in Figure 1.

Figure 1  assigns the query content to a local variable
SQL statement as follows:
use     student
Declare @songname    char (
select  @songname = course Content   from  Course where course category = ' Art class '
print   @songname
SELECT statement assignments and queries cannot be confused, such as declaring a local variable named @ B and assigning a value to the following SQL statement:
declare  @b  int
select  @b=1
Another way to assign a value to a local variable is to use the SET statement. The common syntax for assigning variables using the SET statement is as follows:
{set  @varible_name   =  ecpression} [,... N]
is a simple assignment statement:
Declare&nbs P @song   Char
set  @song   =  ' i  love  flower '
can also assign values to multiple variables, and the corresponding SQL statement looks like this:
declare  @b  int, @c  char (TEN),@a  int
Select @b=1, @c= ' love ', @a=2
Note: There are some keywords in the database language and the programming language, A keyword is a group of characters that can cause an action to occur in an environment. To avoid conflicts and generate errors, you should avoid using keywords when naming tables, columns, variables, and other objects.