Variables Fundamentals



We have used some values such as 242 or 'James Knight'. These are constant values because we certainly know them before using them and we did not change them in our statements. If you intend to use a certain category of value over and over again, you can declare a variable for it. Like C#, the SQL supports variables.


Declaring Variables

To declare a variable, use the DECLARE keyword using the following formula:


The DECLARE keyword lets the interpreter know that you are declaring a variable. The DECLARE keyword is followed by a name for the variable. In Transact-SQL, 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:


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, to name our variable, here are the rules we will follow:

  • 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 or @act_52_t
  • A name will not include special characters such as !, @, #, $, %, ^, &, or *
  • If the name is a combination of words, each word will start in uppercase. Examples are @DateHired, @_RealSport, or @DriversLicenseNumber

To declare a variable, 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 uses the following formula:

DECLARE @VariableName DataType;

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

DECLARE @Variable1 DataType1, @Variable2 DataType2, @Variable_n DataType_n;

Unlike many other languages like C/C++, C#, Java, 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.

Initializing a Variable

After declaring a variable, the interpreter reserves a memory for it but the space does not 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


SET @VariableName = DesiredValue

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



Published on Monday 25 December 2007


Home Copyright © 2007 FunctionX, Inc. Next