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:
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
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.
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:
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:
1> 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)
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:
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)
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.
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.
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.
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
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;
Transact-SQL supports geographical locations.
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.
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.
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.
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:
You should know that these operations can be performed on natural or decimal numbers.