Data Types

Boolean Variables A Boolean value is a piece of information stated as being true or false. To declare a variable that holds a Boolean value, if you are using the MSDE, 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. If you are using MySQL, when declaring the variable, assign it a TRUE or a FALSE value. Practical Learning: Using Boolean Variables To use a Boolean variable, execute the following statement:   MySQL mysql> SET @IsMarried = TRUE; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @IsMarried AS 'IsMarried?'; +------------+ | IsMarried? | +------------+ | 1 | +------------+ 1 row in set (0.00 sec) mysql>   MSDE 1> DECLARE @IsMarried BIT 2> SET @IsMarried = 1 3> SELECT @IsMarried AS [Is Married?] 4> GO Is Married? ----------- 1 (1 row affected)

 

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 read, 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 or the Integer keyword as data type. Here is an example for an MSDE statement: DECLARE @Category int SET @Category = 208 PRINT @Category GO   If using MySQL, when declaring the variable, assign it a natural number to indicate that the variable is an integer.

Practical Learning: Using Integer Variables

To use integer variables, execute the following statement:    MySQL mysql> SET @EmplStatus = 2; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @IsMarried:=TRUE AS 'Is Married?',@EmplStatus:=2 AS 'Employment Status'; +--------------+-------------------------+ | Is Married? | Employment Status | +--------------+-------------------------+ | 1 | 2 | +--------------+--------------------------+ 1 row in set (0.00 sec) mysql> MSDE DECLARE @IsMarried bit, @EmplStatus int SET @IsMarried = 1 SET @EmplStatus = 2 SELECT @IsMarried AS [Is Married?], @EmplStatus AS [Employment Status] GO

 

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, if using MSDE, declare the variable using the numeric or the decimal (either decimal or numeric would produce the same effect in MSDE) data type. If you are using MySQL, to declare a decimal variable, assign a real number to the variable.

Practical Learning: Using Decimal Variables

To use decimal variable, execute the following statement:    MySQL mysql> SET @WeeklyHours := 36.50; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @IsMarried:=FALSE, @EmplStatus:=1, @WeeklyHours; +-------------------------+----------------------+-------------------+ | @IsMarried:=FALSE | @EmplStatus:=1 | @WeeklyHours | +-------------------------+----------------------+--------------------+ | 0 | 1 | 36.5 | +-------------------------+----------------------+--------------------+ 1 row in set (0.05 sec) mysql> MSDE 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

Currency Variables in MSDE

If a variable would hold monetary values and if using MSDE, you can declare it with the money or the smallmoney data type. 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.

 

Date and Time Variables

A datetime data type is used for a variable whose data would consist of date and/or time values. The entries must be valid date or time values. In MSDE, 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.

 

Practical Learning: Using Date/Time Variables

Change the statement as follows:   MySQL mysql> SET @DateHired = '1998/06/22'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @DateHired As 'Date Hired',@EmplStatus AS 'Empl Status', @IsMarried AS 'Married?',@WeeklyHours AS 'Hours', @HourlySalary AS 'Hourly'; +---------------+----------------+------------+--------+---------+ | Date Hired | Empl Status | Married? | Hours | Hourly | +---------------+----------------+------------+--------+---------+ | 1998/06/22 | 1 | 0 | 36.5 | 22.85 | +---------------+----------------+------------+--------+---------+ 1 row in set (0.00 sec) mysql> MSDE 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 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, if using MSDE, you can 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.

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

If you are using MySQL, simply assign a single-quoted string to a variable to indicate that it is text-based.