Introduction to Variables
Introduction to Variables
Variables Fundamentals
Introduction
We know how to use some values such as 242 or 'James Knight'. These types of values are referred to as constant because we certainly know them before their use and we don'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 value. This allows you to put the value in an area of the computer's memory, easily change the value for another, over and over. To use the same area of memory to store and remove values as needed, the SQL 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, use the DECLARE keyword using the following formula:
DECLARE @VariableName DataType
The DECLARE keyword lets the interpreter know that you are making a declaration. In Transact-SQL, the name of a variable starts with the @ sign. Whenever you need to refer to the variable, you must include the @ sign. The name of a variable allows you to identify the area of memory where the value of the variable is stored. Transact-SQL is very flexible when it comes to names. For example, a name can be made of digits only. Here is an example:
DECLARE @264
There are rules and suggestions you will use for the names:
When declaring a variable, after giving a name, you must also specify its data type.
You can declare more than variable at the same time. 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.
After declaring a variable, the interpreter reserves 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.
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. This time, you can type the name of the variable to the right side of PRINT or SELECT.
|
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. The types used for variables are exactly those we used for columns. This also means that the rules we reviewed for those data types are the same. The data types are reviewed here simply as reminders.
A Boolean variable is declared using the BIT or bit data type. 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:
|
|
DECLARE @IsMarried bit SET @IsMarried = 1 SELECT @IsMarried AS [Is Married?]; GO
|
Integers |
Transact-SQL supports various types of natural numbers. 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 data type. Here is an example:
DECLARE @Category int; SET @Category = 1450; PRINT @Category; GO
This would produce 1450:
If the variable will hold very small positive numbers that range from 0 to 255, declare it using the tinyint data type. Here is an example:
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. Here is an example:
Practical
Learning: Introducing Variables1> DECLARE @NumberOfPages SMALLINT;
2> SET @NumberOfPages = 16;
3> SELECT @NumberOfPages AS [Number of Pages];
4> GO
Number of Pages
---------------
16
(1 rows affected)
The bigint data type is used for variables that use small or very large numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Here is an example:
1> DECLARE @CountryPopulation BigInt;
2> SET @CountryPopulation = 16500000;
3> SELECT @CountryPopulation AS 'Country Population';
4> GO
Country Population
--------------------
16500000
(1 rows affected)
|
|
DECLARE @IsMarried bit, @EmplStatus int;
SET @IsMarried = 1;
SET @EmplStatus = 2;
SELECT @IsMarried AS [Is Married?],
@EmplStatus AS [Employment Status];
GO
|
Decimal Numbers |
Transact-SQL supports decimal numbers of all types. For example, you can use the numeric or decimal data type for a variable that would hold all types of numbers, whether natural or decimal. Here is an example:
1> DECLARE @Distance DECIMAL; 2> SET @Distance = 648.16; 3> PRINT @Distance; 4> GO 648
The precision of a decimal number specifies the number of digits used to display the value. As seen already, to specify the precision of a decimal or numeric data type, add some parentheses to the data type. In the paretheses, enter a number between 1 and 38.
The scale specifies the fractional part of a decimal number. It is set on the right side of the period (in US English). Here is an example:
|
|
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
Transact-SQL supports floating-point numbers through the float and the real data types. Here is an example of declaring and using a variable of type float:
1> DECLARE @Radius FLOAT;
2> SET @Radius = 48.16;
3> SELECT @Radius AS Radius;
4> GO
Radius
------------------------
48.159999999999997
(1 rows affected)
|
Currency Values |
If you want the variable to use monetary values, declare it with the money data type. Here is an example:
1> DECLARE @YearlyIncome Money;
2> SET @YearlyIncome = 48500.15;
3> SELECT @YearlyIncome AS [Yearly Income];
4> GO
Yearly Income
---------------------
48500.1500
(1 rows affected)
Remember that Transact-SQL also supports the smallmoney data type whose values range from -214,748.3648 to 214,748.3647. The precision and scale of a money or smallmoney variable are fixed by Microsoft SQL Server. The scale is fixed to 4.
|
|
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
To declare a variable that uses a character or any kind of symbol, use the char data type. To initialize the variable, include its value in single-quotes. Here is an example:
1> DECLARE @Gender char; 2> SET @GENDER = 'M'; 3> SELECT @Gender AS Gender; 4> GO Gender ------ M (1 rows affected)
If the variable deals with international characters or non-Latin symbols (Unicode), use the nchar data type. When initializing the variable, you should procede its value with N. Here is an example:
1> DECLARE @Gender nchar;
2> SET @GENDER = N'M';
3> SELECT @Gender AS Gender;
4> GO
Gender
------
M
(1 rows affected)
A string is a combination of characters or symbols of any kind. To declare a variable for such a value, use the varchar data type. Here is an example:
DECLARE @FirstName varchar;
Remember that you can (in fact should always) specify the length of the string by passwing a number in the parentheses of the data type. Here are examples:
DECLARE @Gender char(1); DECLARE @FirstName varchar(20);
You can then initialize the variable(s) by including its value in single-quotes. Here are examples:
DECLARE @Gender char(1); DECLARE @FirstName varchar(20); SET @Gender = 'Male'; SET @FirstName = 'Yolanda';
If you are using the Command Prompt (SQLCMD.EXE), include its value between double-quotes. Here is an example:
If you are using a Query Editor, don't include the string value in double-quotes; otherwise, you would receive an error.
If the variable may involve international characters or symbols (Unicode), you should declare it using the nvarchar data type . When initializing the variable, precede its value with N. Here are examples:
DECLARE @Gender char; DECLARE @Code nchar; DECLARE @FirstName varchar; DECLARE @LastName nvarchar; SET @Gender = N'Male'; SET @Code = N'7HHF-294'; SET @FirstName = 'Yolanda'; SET @LastName = N'Williamson';;
Notice that you can initialize the variable with N' whether it was declared as char, nchar, varchar, or nvarchar. You will not receive an error.
If you include more than one character in the single-quotes, only the first (most left) character would be stored in the variable.
|
|
DECLARE @FirstName nvarchar(20),
@LastName nvarchar(20),
@FullName nvarchar(40),
@EmplStatus int,
@IsMarried bit,
@WeeklyHours Decimal(6,2),
@HourlySalary SmallMoney,
@WeeklySalary SmallMoney;
SET @FirstName = N'Samuel';
SET @LastName = N'Weinberg';
SET @FullName = @LastName + N', ' + @FirstName;
SET @IsMarried = 1;
SET @EmplStatus = 2;
SET @WeeklyHours = 36.50;
SET @HourlySalary = 15.72;
SET @WeeklySalary = @WeeklyHours * @HourlySalary;
SELECT @FullName As [Full Name],
@EmplStatus AS [Empl Status],
@IsMarried AS [Married?],
@WeeklyHours AS Hours,
@HourlySalary AS Hourly,
@WeeklySalary AS Weekly;
GO
If the variable will use large text, declare it using the varchar(max) data type. If the text may involve Unicode characters, declare it using the nvarchar(max) data type. Here is an example:
declare @TermPaper nvarchar(max);
You can initialize the variable using any of the rules we reviewed for strings.
|
SQL Variants |
Transact-SQL provides the sql_variant data type. If can be used to declare a variable that can hold any type of value. When initializing the variable, you must follow the rules of the actual data type the SQL variant represents. Here are examples:
DECLARE @FullName SQL_VARIANT,
@DateHired Sql_Variant,
@IsMarried SQL_variant,
@YearlyIncome sql_variant;
SET @FullName = N'Paul Yamo';
SET @DateHired = N'20110407';
SET @IsMarried = 1;
SET @YearlyIncome = 48500.15;
SELECT @FullName AS [Full Name];
SELECT @DateHired AS [Date Hired];
SELECT @IsMarried AS [Is Married?];
SELECT @YearlyIncome AS [Yearly Income];
GO
|
Geometric Types |
Transact-SQL support geometric coordinates through the geometry data type. You can use it to declare a variable. Here is an example:
DECLARE @Location geometry;
The geometry type is a class with properties and methods. After declaring a geometry variable, you must initialize it. The most fundamental action is to initialize the variable. This is done through the STGeomFromText method whose syntax is:
static geometry STGeomFromText('geography_tagged_text', SRID)
The method is static. This means that, to access it, you use geometry::STGeomFromText. Here is an example:
DECLARE @Location geometry; SET @Location = geometry::STGeomFromText(. . .)
This method takes two arguments. The first argument is holds a value identified as a Well-Known Text (WKT) value. The value follows a format defined by OGC. There are various you can specify this value. As you may know already, a geometric point is an object that has two values: the horizontal coordinate x and the vertical coordinate y. The value can be integers or flowing-point numbers.
If you know the coordinates of a point and you want to use it as the value of the geometry object, type point() (or POINT(), this is not case-sensitive) and, in the parentheses, type both values separated by a space. Here is an example:
DECLARE @Location geometry;
SET @Location = geometry::STGeomFromText('point(6 4)', . . .
Instead of just one point, you may want to use a geometric value that is a line. In this case, specify the shape as linestring(, ). In the parentheses and on both sides of the comma, type each point as x and y. Here is an example:
DECLARE @Location geometry;
SET @Location = geometry::STGeomFromText('linestring(1 4, 5 2)', . . .);
You can also use a complex geometric, in which case you can pass the argument as a polygon. Use polygon(()) (or POLYGON(())) and pass the vertices in the parentheses. Each vertext should specify its x and y coordinates. The vertices are separated by commas. A last vertex should be used to close the polygon, in which case the first and the last vertices should be the same. Here is an example:
DECLARE @Location geometry;
SET @Location = geometry::STGeomFromText('polygon((1 2, 2 5, 5 5, 4 2, 1 2))', . . );
The second argument of the geometry::STGeomFromText method is a contant integer known as the spatial reference ID (SRID).
After declaring and initializing the value, you can use a SELECT statement to display its value. Here is an example:
DECLARE @Location geometry;
SET @Location = geometry::STGeomFromText('point(6 4)', 0);
SELECT @Location;
|
Representing a Geographical Location |
Transact-SQL supports geographical locations.
|
User-Defined Types |
Transact-SQL allows you to define a type based on one of the existing data type. This is called a user-defined data type (UDT). We have already reviewed how to create it. Here are examples:
CREATE TYPE NaturalNumber FROM int; GO CREATE TYPE ShortString FROM nvarchar(20); GO CREATE TYPE ItemCode FROM nchar(10); GO CREATE TYPE LongString FROM nvarchar(80); GO CREATE TYPE Salary FROM decimal(8, 2); GO CREATE TYPE Boolean FROM bit; GO
After creating a UDT, you can declare a variable for it. Then, before using it, you must initialize it with the appropriate value. Here are examples:
DECLARE @EmployeeID NaturalNumber,
@EmployeeNumber ItemCode,
@FirstName ShortString,
@LastName ShortString,
@Address LongString,
@HourlySalary Salary,
@IsMarried Boolean;
SET @EmployeeID = 1;
SET @EmployeeNumber = N'28-380';
SET @FirstName = N'Gertrude';
SET @LastName = N'Monay';
SET @Address = N'1044 Alicot Drive';
SET @HourlySalary = 26.75;
SET @IsMarried = 1;
SELECT @EmployeeID AS [Empl ID], @EmployeeNumber AS [Empl #],
@FirstName AS [First Name], @LastName AS [Last Name],
@Address, @HourlySalary AS [Hourly Salary],
@IsMarried AS [Is Married ?];
GO
Of course, you can mix Transact-SQL data types and your own defined type in your code.
Composite Operations
Introduction
A composite operation consists of performing the operation from a variable to itself. For example, suppose you have a variable a that has a value and you want to change the value of that variable by adding its own value to itself. Composite operations use an operator that is in fact a combination of two operators. The variable can be almost any type that supports the type of operation you want to perform.
|
Example: The Composite Addition |
The composite operation uses the += operator. Using it, to add the value of a variable to itself, type the variable and insert this operation between both operands. Here is an example:
DECLARE @Variable int;
SET @Variable = 248;
SELECT @Variable;
SET @Variable += @Variable;
SELECT @Variable;
Once you have performed the operation, the variable holds the new value. Consider this:
As mentioned already, a variable that is involved in a composite operation can be of any type as long as the type supports that operation. For example, strings in Transact-SQL support the addition. This means that the variable can be of type char or any of its variants.
One variant of the composite operation is to add oone variable to another. To do this, include the += operator between the operants. Here is an example:
DECLARE @Name nvarchar(50);
DECLARE @LastName nvarchar(20);
SET @Name = N'Paul';
SET @LastName = N' Yamaguchi';
SELECT @Name;
SELECT @LastName;
SET @Name += @LastName;
SELECT @Name;
When the operation has been performed, the left operand now holds its value and that of the other variable:
Another variant of the composite operation consists of adding a constant to a variable. In this case, on the right side of the += operator, use the constant. Here is an example:
DECLARE @FirstName nvarchar(50);
SET @FirstName = N'Paul';
SELECT @FirstName;
SET @FirstName += N' Motto';
SELECT @FirstName;
Once again, remember that when the operation has been performed, the variable holds the new value. Here is an example:
In the same way, you can perform this operation as many time as you want by adding right operands to a left operands. Here are examples:
DECLARE @Name nvarchar(50); DECLARE @MiddleName nvarchar(20); DECLARE @LastName nvarchar(20); SET @Name = N'Paul'; SET @MiddleName = N' Bertrand'; SET @LastName = N' Yamaguchi'; SET @Name += @MiddleName; SELECT @Name; SET @Name += @LastName; SELECT @Name;
One important thing you must keep in mind is the storage capacity of the left operand: It must be able to hold all values added to it.
|
Other Binary operations |
The concept of composite operation can be applied to all arithmetic binary operations. As seen above, strings also support the addition composite operation. Composite operations are also available on all bit manipulation operations. The most important thing to remember is that not all data types support all operations. Overall:
DECLARE @Number int; SET @Number = 215; SET @Number &= 15; SELECT @Number;
DECLARE @Number int; SET @Number = 215; SET @Number |= 15; SELECT @Number;
DECLARE @Number int; SET @Number = 215; SET @Number ^= 15; SELECT @Number;
You should know that these operations can be performed on natural or decimal numbers.
|
|
|
|
||
| Previous | Copyright © 2000-2022, FunctionX | Next |
|
|
||