Home

Variables and Data Types
 

Variables Fundamentals
 

Introduction

The values we have used so far 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 numbers over and over again, you can reserve a section of memory for that category. This allows you to put a number in that area of memory, easily change the number with another, over an over. For example, you can store the names of employees one after another in the same area of memory as needed. To manage this exchange of memory, Transact-SQL uses its 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.

Practical Learning Practical Learning: Introducing Variables

  • Open the SQL Query Analyzer

Declaring Variables

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, 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 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:

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.

Objects Names

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, 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 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.

Initializing a Variable

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.

Data Types
 

Introduction

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.

An application deals with various types of data. This means that you should take care of jobs behind the scenes as much as you can. One way you can do this is by controlling the amount of information that can be entered in a particular field. As various columns can hold different types of data, so can the same data type control its own mechanism of internal data entry. The length of data means different things to different fields. Fields that carry the same data type can have different lengths.

Boolean Variables

A Boolean value is a piece of information stated as being true or false, On or Off, Yes or No, 1 or 0. To declare a variable that holds a Boolean value, you can use the BIT or bit keyword. Here is an example:

DECLARE @IsOrganDonor bit

After declaring a Boolean variable, you can initialize it with 0 or another value. If the variable is initialized with 0, it receives the Boolean value of False. If it is initialized with any other number, it receives a True value. Here is an example of using a Boolean variable:

 

Practical Learning Practical Learning: Using Boolean Variables
  1. In the Query window, type the following:
      
    DECLARE @IsMarried bit
SET @IsMarried = 1
SELECT @IsMarried AS [Is Married?]
GO
  2. Execute the statement

Integer Variables

An integer, also called a natural number, or a whole number, is a number that can start with a + or a - sign and is made of digits. Between the digits, no character other than a digit is allowed. In the real world, when a number is (very) long and becomes difficult to ready, such as 79435794, you are allowed to type a symbol called the thousand separator in each thousand increment. An example is 79,435,794. In your SQL expressions, never include the thousand separator: you would receive an error.

When the number starts with +, such as +44 or +8025, such a number is referred to as positive and you should omit the starting + sign. This means that the number should be written as 44 or 8025. Any number that starts with + or simply a digit is considered as greater than 0 or positive. A positive integer is also referred to as unsigned. On the other hand, a number that starts with a - symbol is referred to as negative.

If a variable would hold natural numbers in the range of -2,147,483,648 to 2,147,483,647, you can declare it with the int keyword as data type. Here is an example:

DECLARE @Category int
SET @Category = 208
PRINT @Category
GO

The length of an integer is the number of bytes its field can hold. For an int type, that would be 4 bytes.

If you except to use very small numbers such as student's ages, or the number of pages of a brochure or newspaper, apply the tinyint data type to such a field. A variable with the tinyint data type can hold positive numbers that range from 0 to 255.

The smallint data type follows the same rules and principles as the int data type except that it is used to store smaller numbers that would range between -32,768 and 32,767.

The bigint data type follows the same rules and principles as the int data type except that its field can hold numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

The binary data type is used for a variable that would hold hexadecimal numbers. Examples of hexadecimal numbers are 0x7238, 0xFA36, or 0xAA48D. Use the binary data type if all values of the variable would have the exact same length (or quantity). If you anticipate that some entries would be different than others, then use the alternative varbinary data type. The varbinary type also is used for hexadecimal numbers but allows dissimilar entries, as long as all entries are hexadecimals.

Practical Learning Practical Learning: Using Integer Variables
  1. Change the statement as follows:
      
    DECLARE @IsMarried bit, @EmplStatus int
SET @IsMarried = 1
SET @EmplStatus = 2
SELECT @IsMarried AS [Is Married?],
       @EmplStatus AS [Employment Status]
GO
  2. Execute the statement:
     

Decimal Variables

A decimal number is a number that can have a period (or the character used as the decimal separator as set in the Control Panel) between the digits. An example would be 12.125 or 44.80. Like an integer, a decimal number can start with a + or just a digit, which would make it a positive number. A decimal number can also start with a - symbol, which would make it a negative number. If the number represents a fraction, a period between the digits specifies what portion of 1 was cut. If you anticipate such a number for a field, specify its data type as numeric or decimal (either decimal or numeric would produce the same effect in SQL Server). Here is an example:

A floating-point number is a fractional number, like the decimal type. Floating-point numbers can be used if you would allow the database engine to apply an approximation to the actual number that a field is supposed to carry. To declare such a variable, use the float or the real keyword.

A precision is the number of digits used to display a numeric value. For example, the number 42005 has a precision of 5, while 226 has a precision value of 3. If the data type is specified as an integer (the int and its variants) or a floating-point number (float and real), the precision is fixed by the database and you can just accept the value set by SQL Server. For a decimal number (decimal or numeric data types), SQL Server allows you to specify the amount of precision you want. The value must be an integer between 1 and 38 (28 if you are using SQL Server 7).

A decimal number is a number that has a fractional section. Examples are 12.05 or 1450.4227. The scale of a number if the number of digits on the right side of the period (or the character set as the separator for decimal numbers for your language, as specified in Control Panel). The scale is used only for numbers that have a decimal part, which includes currency (money and smallmoney) and decimals (numeric and decimal). If a variable is declared with the decimal or numeric data type, you can specify the amount of scale you want. The value must be an integer between 0 and 18. Here is an example:

Practical Learning Practical Learning: Using Decimal Variables
  1. Change the statement as follows:
      
    DECLARE @IsMarried bit,
        @EmplStatus int,
        @WeeklyHours Decimal(6,2)
SET @IsMarried = 1
SET @EmplStatus = 2
SET @WeeklyHours = 36.50
SELECT @IsMarried AS [Is Married?],
       @EmplStatus AS [Employment Status],
       @WeeklyHours AS Hours
GO
  2. Execute the statement

Currency Variables

If a variable would hold monetary values, you can declare it with the money keyword. A variable with a money data type can hold positive or negative values from -922,337,203,685,477.5808 to +922,337,203,685,477.5807. Here is an example:

DECLARE @YearlyIncome Money
SET @YearlyIncome = 20800.12
SELECT @YearlyIncome
GO

While the money data type can be used for a variable that would hold large quantities of currency values, the smallmoney data type can be applied for a variable whose value cannot be lower than -214,748.3648 nor higher than 214,748.3647.

The precision and scale of a money or smallmoney variable are fixed by SQL Server. The scale is fixed to 4.

Practical Learning Practical Learning: Using Currency Variables
  1. Change the statement as follows:
      
    DECLARE @EmplStatus int,
        @IsMarried bit,
        @WeeklyHours Decimal(6,2),
        @HourlySalary SmallMoney,
        @WeeklySalary SmallMoney
SET @IsMarried = 1
SET @EmplStatus = 2
SET @WeeklyHours = 36.50
SET @HourlySalary = 15.72
SET @WeeklySalary = @WeeklyHours * @HourlySalary
SELECT @EmplStatus AS [Empl Status],
       @IsMarried AS [Married?],
       @WeeklyHours AS Hours,
       @HourlySalary AS Hourly,
       @WeeklySalary AS Weekly
GO
  2. Execute the statement
     

Date and Time Variables

A datetime data type is used for a column whose data would consist of date and/or time values. The entries must be valid date or time values but SQL Server allows a lot of flexibility, even to display a date in a non-traditional format. The date value of a datetime field can be comprised between January 1st, 1753 and December 31, 9999.

The smalldatetime is an alternative to the datetime data type. It follows the same rules and principles as the datetime data type except that a date value must be comprised between January 1st, 1900 and June 6, 2079.

To initialize a date or time-based variable, include the desired but recognizable value in single-quotes. Here is an example:
 

Practical Learning Practical Learning: Using Date/Time Variables
  1. Change the statement as follows:
      
    DECLARE @DateHired DateTime,
        @EmplStatus int,
        @IsMarried bit,
        @WeeklyHours Decimal(6,2),
        @HourlySalary SmallMoney,
        @WeeklySalary SmallMoney
SET @DateHired = '12/05/1998'
SET @IsMarried = 1
SET @EmplStatus = 2
SET @WeeklyHours = 36.50
SET @HourlySalary = 15.72
SET @WeeklySalary = @WeeklyHours * @HourlySalary
SELECT @DateHired AS [Date Hired],
       @EmplStatus AS [Empl Status],
       @IsMarried AS [Married?],
       @WeeklyHours AS Hours,
       @HourlySalary AS Hourly,
       @WeeklySalary AS Weekly
GO
  2. Execute the statement

Character Variables

A field of characters can consist of any kinds of alphabetical symbols in any combination, readable or not. If you want a variable to hold a fixed number of characters, such as the book shelf numbers of a library, declare it with the char data type.

In the computer world, a string is a character or a combination of characters that are considered "as is" with regards to the scenario in which they are used. If a variable will hold strings of different lengths, declare it with the varchar data type. The maximum length of text that a field of varchar type can hold is equivalent to 8 kilobytes.

In some circumstances, you will need to change or specify the number of characters used in a varchar variable. Although a First Name and a Book Title variables should use the varchar type, both variables would not have the same length of entries. As it happens, people hardly have a first name that is beyond 20 characters and many book titles go beyond 32 characters. In this case, both variables would use the same data type but different lengths. To specify the maximum number of characters that can be stored in the variable, on the right side of varchar, type an opening and a closing parentheses. Inside of the parentheses, type the desired number. Here is an example: 

DECLARE @Welcome varchar(50)
SET @Welcome = 'Welcome to the world of Transact-SQL'
PRINT @Welcome
GO

The text data type can be applied to a field whose data would consist of ASCII characters. As opposed to a varchar type of field, a text type of field can hold text that is longer than 8 kilobytes.

The nchar, nvarchar, and ntext types follow the same rules as the char, varchar, and text respectively, except that they can be applied to variables that would hold international characters, that is, characters of languages other than US English. This is done following the rules of Unicode formats.

Practical Learning Practical Learning: Using Character Variables
  1. Change the statement as follows:
      
    DECLARE @FirstName varchar(20),
        @LastName varchar(20),
        @FullName varchar(40),
        @DateHired DateTime,
        @EmplStatus int,
        @IsMarried bit,
        @WeeklyHours Decimal(6,2),
        @HourlySalary SmallMoney,
        @WeeklySalary SmallMoney
SET @FirstName    = 'Samuel'
SET @LastName     = 'Weinberg'
SET @FullName     = @LastName + ', ' +@FirstName
SET @DateHired    = '12/05/1998'
SET @IsMarried    = 1
SET @EmplStatus   = 2
SET @WeeklyHours  = 36.50
SET @HourlySalary = 15.72
SET @WeeklySalary = @WeeklyHours * @HourlySalary
SELECT @FullName As [Full Name],
       @DateHired AS [Date Hired],
       @EmplStatus AS [Empl Status],
       @IsMarried AS [Married?],
       @WeeklyHours AS Hours,
       @HourlySalary AS Hourly,
       @WeeklySalary AS Weekly
GO
  2. Execute the statement
     
  3. Save the file as Variables in your My Documents folder
 
Previous Copyright © 2005-2016, FunctionX Next