Variables

The values we used in the previous lesson are referred to as constant because we certainly knew them in advance and didn't change them in our statements. If you intend to use a certain category of value over and over again, you can reserve a section of memory for that category. This allows you to put a value in that area of memory, easily change the value with another, over and over. For example, you can store the names of employees one after another in the same area of memory as needed. This exchange of values with the computer memory can be managed by the interpreter.

To use the same area of memory to store and remove values as needed, the interpreter needs two primary pieces of information: a name and the desired amount of space in memory capable of storing the value.

A variable is an area of memory used to store values that can be used in a program. Before using a variable, you must inform the interpreter. This is also referred to as declaring a variable. To declare a variable, if you are using MSDE, use the DECLARE keyword using the following formula:

DECLARE Options

The DECLARE keyword lets the interpreter know that you are making a declaration. The DECLARE keyword is followed by a name for the variable. In MSDE, the name of a variable starts with the @ sign. The name of a variable allows you to identify the area of memory where the value of the variable is stored. While other languages like C/C++, Pascal, Java, C#, etc impose strict rules to names, Transact-SQL is extremely flexible. A name can be made of digits only. Here is an example:

DECLARE @264

Such a name made of digits can create confusion with a normal number. A name can also be made of one or more words.

To avoid confusion, here are the rules we will use on this site:

• A name will start with either an underscore or a letter. Examples are @_n, @act, or @Second
• After the first character as an underscore or a letter, the name will have combinations of underscores, letters, and digits. Examples are @_n24, @act_52_t
• A name will not include special characters such as !, @, #, $, %, ^, &, or *
• We will avoid using spaces in a name, with few exceptions 
• If the name is a combination of words, each word will start in uppercase. Examples are @DateHired, @_RealSport, or @DriversLicenseNumber

To declare a variable, if you are using MSDE, as we will see in the next sections, after giving a name to a variable, you must also specify the amount of memory that the variable would need. The amount of memory is also called a data type. Therefore, the declaration of a variable in MSDE uses the following formula:

DECLARE @VariableName DataType

With MSDE, you can also declare more than one variable. To do that, separate them with a comma. The formula would be:

DECLARE @Variable1 DataType, @Variable2 DataType, @Variable1 DataType

Unlike many other languages like C/C++ or Pascal, if you declare many variables that use the same data type, the name of each variable must be followed by its own data type.

After declaring a variable, the interpreter reserves a space in the computer memory for it but the space doesn't necessarily hold a recognizable value. This means that, at this time, the variable is null. One way you can change this is to give a value to the variable. This is referred to as initializing the variable.

Remember that a variable's name starts with @ and whenever you need to refer to the variable, you must make sure you include the @ sign. To initialize a variable, in the necessary section, type the SELECT or the SET keyword followed by the name of the variable, followed by the assignment operator "=", followed by an appropriate value. The formula used is:

SELECT @VariableName = DesiredValue

or

SET @VariableName = DesiredValue

Once a variable has been initialized, you can make its value available, or display it, to the user. This time, you can type the name of the variable to the right side of PRINT or SELECT.

Introduction to Data Types

After setting the name of a variable, you must specify the amount of memory that the variable will need to store its value. Since there are various kinds of information a database can deal with, SQL provides a set of data types. Therefore, you must specify the data type that is necessary for a particular column.

MySQL Variables

Introduction

Like the MSDE, MySQL also supports variables. The name of a variable starts with @ then follows the rules and suggestions we reviewed above. In MySQL, you declare a variable and initialize it at the same time, using the SET keyword. Here is an example:

mysql> SET @Age = 16;
Query OK, 0 rows affected (0.00 sec)

After this assignment, the SQL interpreter would analyze the type of value that was assigned to the variable and allocate the appropriate amount of memory.

Declaring Various Variables

In MySQL, after declaring a variable, it is stored in memory. You can later use it as you wish. If you need one or more other variables, you can additionally declare each. Here are examples:

Notice that, in this case, the Age, the FullName, the City, and the HourlySalary variables have each been declared and separately. Instead of declaring one variable at a time, you can declare many variables at the same time. To do this, type SET followed by the name of each variable assigned with the desired value and separated by commas. This time, the assignment must be performed with the := operator and not the = operator. Here are examples:

mysql> SET @Age:=16, @FullName:='John Doe', @City:='Sydney', @Salary:=22.85;
Query OK, 0 rows affected (0.00 sec)

The := assignment operator can also be used if you are declaring only one variable.