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. |
 |
Practical Learning: Introducing 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.
The primary formula to declare a variable is:
DECLARE @variable-name . . .[;]
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. 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:
Introduction to the Type of a Variable
When declaring a variable, after giving a name, you must also specify its data type. The formula to follow is:
DECLARE @variable-name AS data-type[;]
This means that, after the name of the variable, add a space, the AS, and a data type. You can optionally end the statement with a semicolon.
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. One way to take care of this is to give a value to a variable when declaring it. This operation is referred to as initializing the variable.
The primary formula to initialize a variable is:
DECLARE @variable-name data-type AS data-type = desired-value[;]
This means that, after the data type, type = followed by a value. Once again, you can add or omit a semicolon.
Introduction to Character Types
When it comes to application, a character is any symbol, readable or not, you can think of. Examples of characters are the letters of the alphabet and the writing signs. To declare a variable for a character or any kind of symbol, use a data type named char. To initialize the variable, include its value in single-quotes. Here is an example:
1> DECLARE @gender char = 'M';
2> SELECT @gender AS Gender;
3> GO
Gender
------
M
(1 rows affected)
Practical Learning: Declaring a Variable
DECLARE @gender char = 'M'; SELECT @gender AS Gender; GO
As another option to represent a characer, Transact-AQL provides a data type named varchar. You can use it to create a variable that would hold a symbol of any type.
If the variable deals with international characters or non-Latin symbols (Unicode), use a data type named nchar. Here is an example:
1> DECLARE @gender nchar[;]
If you want to initialize the variable, you should procede its value with N. Here is an example:
1> DECLARE @gender nchar = N'M';
2> SELECT @gender AS Gender;
3> GO
Gender
------
M
(1 rows affected)
Practical Learning: Declaring a Unicode Variable
DECLARE @gender nchar = N'M'; SELECT @gender AS Gender; GO
Introduction to Strings
A string is a combination of characters or symbols of any kind. To declare a variable for such a value, you can use any of the character data types we saw above: the char, the nchar, or the varchar.
Introduction to the Length of a String
Remember that a character type is meant to hold a single symbol. A string is a group of characers. To indicate that you want a variable to hold more than one symbol, you must specify the number of characters for the string. This number of characters is also referred to as the length of the string. To specify the length of a string, add some parentheses to one of the above data types. Inside the parentheses, type a natural number. This will be the maximum number of characters that the string will be able to hold. Here are examples:
DECLARE @gender char(6); DECLARE @firstName varchar(12);
To initialize the variable, assign a single-quoted value to it. In the quotes, put the symbolds you want. Here are examples:
DECLARE @gender char(6) = 'Female'; DECLARE @firstName varchar(12) = 'Yolanda'; SELECT @gender AS Gender; SELECT @firstName AS [First Name]; GO
If you are using the Command Prompt (SQLCMD.EXE), include the value between double-quotes.
If you are using a Query Editor, don't include the string value in double-quotes; otherwise, you would receive an error.
If the string may involve international characters or symbols (Unicode), you should declare its variable using a data type named nvarchar. When initializing the variable, precede its value with N. Here are examples:
DECLARE @employeeName NVARCHAR(50) = N'James Williamson'; DECLARE @employmentStatus nvarchar(12) = N'Full-Time'; SELECT @employeeName AS [Employee Name]; SELECT @employmentStatus AS [Employment Status]; GO
A Character from a String
As we have seen above, you can declare a variable using with the char, the nchar, the varchar, or the nvarchar types. You can then initialize the variable. In reality, you can initialize the variable with any number of characters as if it were a string. Here are examples:
DECLARE @gender char = 'Female'; DECLARE @code varchar = 'Complete'; DECLARE @status nchar = 'Senior';
In that case, only the first (most left) character would be stored in the variable. Therefore, when you access the variable, you would get only the first character:
If you want a value to use large text, you can use the varchar() data type. In this case, in the parentheses of the type, enter max, as in varchar(max). If the text may involve Unicode characters, use 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.
Introduction
A natural number is a value that includes one or a combination of digits.
A Tiny Integer
If the value is a very small positive number in the range of 0 to 255, it is referred to as a tiny integer. To declare a variable that must hold such a small number, use a data type named tinyint. Here is an example:
If you want to use a relatively small number that is between -32,768 and 32,767, the data type to use is named smallint. Here is an example of a variable declared with smallint:
1> DECLARE @numberOfPages smallint = 268;
2> SELECT @numberOfPages AS [Number of Pages];
3> GO
Number of Pages
---------------
268
(1 rows affected)
Regular Integers
To provide a simple regular type for natural numbers, Transact-SQL supports a data type named int. A value of int type can be between -2,147,483,648 and 2,147,483,647. Here is an example:
DECLARE @category int = 1450; PRINT @category; GO
This would produce 1450
A Big Integer
If you want to use a number that is extremely low or extremely large, you can use a data type named bigint. This type can handle numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807. Here is an example:
1> DECLARE @countryPopulation BigInt = 16500000; 2> SELECT @countryPopulation AS 'Country Population'; 3> GO Country Population -------------------- 16500000 (1 rows affected)
Fractional Numbers
Introduction
A fractional or decimal number is a value that contains either a natural number or a combination of a natural number and a fraction of 1. Transact-SQL supports various types of decimal numbers.
Decimal Numbers
To let you indicate that a value must be a real number, Transact-SQL provides a data type named numeric. Another name for that type is decimal. You can use one of those types to declare a variable. You can initialize the variable with a natural number or a number with a fraction. Here is an example:
1> DECLARE @distance DECIMAL = 648.16;
2> SELECT @distance;
3> GO
648
The Precision of a Decimal Number
The precision of a decimal number is the number of digits used to display the value. 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 of a Decimal Number
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 a data type named float. Another name for that type is real. You can use one of those types to declare a variable. Here is an example of type float:
1> DECLARE @radius FLOAT = 48.16;
2> SELECT @radius AS Radius;
3> GO
Radius
------------------------
48.159999999999997
(1 rows affected)
Monetary Values
A Regular Money Value
If you want a number for monetary values, Transact-SQL provides a data type named money. You can use it to declare a variable. You can then initialize the variable with a decimal number. Here is an example:
1> DECLARE @yearlyIncome Money = 48500.15;
2> SELECT @yearlyIncome AS [Yearly Income];
3> GO
Yearly Income
---------------------
48500.1500
(1 rows affected)
A Small Money Value
Transact-SQL supports another type for monetary values. The data type is named smallmoney. Its value can be between -214,748.3648 and 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.
Practical Learning: Using Boolean Variables
DECLARE @isMarried bit SET @isMarried = 1 SELECT @isMarried AS [Is Married?]; GO
Other Types
SQL Variants
If you want a value that can hold any type, Transact-SQL provides a data type named sql_variant. You can use it to declare a variable. 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 = N'Paul Yamo';
DECLARE @dateHired Sql_Variant = N'20110407';
DECLARE @isMarried SQL_variant = 1;
DECLARE @yearlyIncome sql_variant = 48500.15;
SELECT @fullName AS [Full Name];
SELECT @dateHired AS [Date Hired];
SELECT @isMarried AS [Is Married?];
SELECT @yearlyIncome AS [Yearly Income];
GO
User-Defined Types
Transact-SQL allows you to define a type based on one of the existing data types. This is called a user-defined data type (UDT). To create a user-defined type, the formula to use is:
CREATE TYPE new-name FROM known-type[;]
Start with the CREATE TYPE expression, followed by a name of your choice. The name should follow the rules and suggestions of names of variables. Add the FROM keyword, followed by one of the data types we have seen so far. 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 using its type. You must initialize the variable with the appropriate value. Here are examples:
DECLARE @employeeId NaturalNumber = 1;
DECLARE @employeeNumber ItemCode = N'28-380';
DECLARE @firstName ShortString = N'Gertrude';
DECLARE @lastName ShortString = N'Monay';
DECLARE @address LongString = N'1044 Alicot Drive';
DECLARE @hourlySalary Salary = 26.75;
DECLARE @isMarried Boolean = 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.
Topics on Declaring Variables
Assigning a Value to a Variable
In previous sections, we saw that, when you declare a variable, you can immediately give it a value. This was referred to as initializing the variable. Sometimes, as your code progress from top-down, you may want the variable to change its value. This is referred to as updating a variable. As a result, at any time, you can change the value of a variable. In other words, at any time, you can update a value once you judge it necessary.
The formula to change the value of a variable is:
SET/SELECT @ variable-name = desired-value[;]
Based on this formula, type the SET or the SELECT keyword, a space, the name of the variable, the = operator, and the desired value. Of course, the value must be appropriate for the type of the variable. You can optionally end the statement with a semicolon. Here are examples:
DECLARE @staffCode INT = 957084; DECLARE @fullName NVARCHAR = N'Gertrude Monay'; DECLARE @status CHAR = 'Full-Time'; DECLARE @salary DECIMAL(6, 2) = 24.77; SELECT @staffCode AS [Employee Number]; SELECT @fullName AS [Employee Name]; SELECT @status AS [Employment Status]; SELECT @salary AS [Hourly Salary]; -- Updating a character variable SELECT @status = 'Seasonal'; -- Updating an integer variable SET @staffCode = 428297; -- Updating a decimal variable SELECT @salary = 30.16; -- Updating a string variable SET @fullName = N'Paul Bertrand Yamaguchi'; SELECT @staffCode AS [Employee Number]; SELECT @fullName AS [Employee Name]; SELECT @status AS [Employment Status]; SELECT @salary AS [Hourly Salary]; GO
So far, we initialized all the variables we have been declared. You are not required to initialize a variable when declaring it. This means that you can declare a variable and not initialize it. Here are examples:
-- An integer variable DECLARE @staffCode INT; -- A string variable DECLARE @fullName NVARCHAR; -- A character variable DECLARE @status CHAR; -- A numeric variable DECLARE @salary DECIMAL(6, 2); GO
If you declare a variable and not initialize it, by default, it receives a value referred to as NULL. Here are examples:
Microsoft Windows [Version 10.0.19045.5679]
(c) Microsoft Corporation. All rights reserved.
C:\Users\monye>SQLCMD
1> DECLARE @staffCode INT;
2> DECLARE @fullName nvarchar;
3> DECLARE @status char;
4> DECLARE @salary Decimal(6, 2);
5> SELECT @staffCode AS [Employee Number];
6> SELECT @fullName AS [Employee Name];
7> SELECT @status AS [Employment Status];
8> SELECT @salary AS [Hourly Salary];
9> GO
Employee Number
---------------
NULL
(1 rows affected)
Employee Name
-------------
NULL
(1 rows affected)
Employment Status
-----------------
NULL
(1 rows affected)
Hourly Salary
-------------
NULL
(1 rows affected)
1>
Therefore, before accessing a variable, you should assign a value to it. The ability to assign a value to a variable allows you to first declare a variable, and then assign a value to it later. Here are examples:
1> DECLARE @staffCode Int; 2> DECLARE @status Char; 3> DECLARE @fullName NvarChar(50); 4> DECLARE @salary Numeric; 5> SET @fullName = N'Michael Carlock'; 6> SET @salary = 68225; 7> SET @staffCode = 947315; 8> SET @status = 'Part-Time'; 9> SELECT @staffCode AS N'Employee Number'; 10> SELECT @fullName AS N'Employee Name'; 11> SELECT @status AS N'Employment Status'; 12> SELECT @salary AS N'Yearly Salary'; 13> GO Employee Number --------------- 947315 (1 rows affected) Employee Name -------------------------------------------------- Michael Carlock (1 rows affected) Employment Status ----------------- P (1 rows affected) Yearly Salary -------------------- 68225 (1 rows affected)
Declaring many Variables
You can declare more than one variable using the DECLARE keyword. The formula to follow is:
DECLARE @variable_1 data-type_1, @variable_2 data-type_2, @variable_n data-type_n;
If you declare many variables that use the same data type, the name of each variable must be followed by its own data type.
Practical Learning: Using String Variables
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
Fundamentals of Operations
Introduction to Operations
An operation is a combination of items (or values) with the goal to produce a new meaning (or value).
Operands
An operand is an item (or value) that is involved in an operation.
Operators
An operator is a symbols that is involved in an operation to combine two other items (such as values) of the operation.
Unary Operators
Introduction
A unary operator is an operator that performs its operation on only one operand. Transact-SQL has a few unary operators available.
The Positive Operator +
A number lower than 0 is referred to as negative while a number higher than is considered positive. To express that a number is positive, you can use the + unary operator. In this case, write a + sign on the left of the number. Examples are +4, +228, +90335.
The Negative Operator -
To indicate that a number is negative, you must apply the - unary operator to it. Examples are -12, -448, -32706. A value accompanied by - is referred to as negative. Here is an example:
SELECT -1250
Other Types of Operations
A binary operation is an operation that involves an operator acting on two operands. In that case, the operator is referred to as a binary operator. Arithmetic provides various binary operators
A ternary operation is an operation that involves three operands.
Composite Operations
A composite operation consists of performing the operation from a variable 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.
Introduction to the Addition
Overview
The addition is a binary operation that consists of adding one item to another. The operation can be performed on two constant values. You can then use PRINT or SELECT to display the operation.
Adding Two Numbers
You can use the addtion operation to add two numbers. Here is an example:
PRINT 125 + 4088
An Addition with a Variable
You can also use the addtion operation to add a number to a constant. Here is an example:
1> DECLARE @number numeric = 9284; 2> SELECT @number AS Original; 3> DECLARE @update numeric = @number + 371385; 4> SELECT @update AS [New Value]; 5> GO Original -------------------- 9284 (1 rows affected) New Value -------------------- 380669 (1 rows affected)
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:
Adding Text
Adding a Character
As seen with numbers, you can add a character to another character or to a string. You can start by declaring a character variable using char, nchar, varchar, or nvarchar. You can then use the + operator to add it to another character or string. Consider the following example:
1> DECLARE @carbon char = 'Carbon'; 2> DECLARE @carbFourteen varchar(5) = @carbon + '14'; 3> SELECT @carbFourteen AS [Carbon 14]; 4> GO Carbon 14 --------- C14 (1 rows affected)
In the same way, you can add two variables that each holds a character. Here is an example:
1> DECLARE @n char = 'New'; 2> DECLARE @y char = 'York'; 3> DECLARE @NewYork nchar(5) = @n + @y; 4> SELECT @NewYork AS [New York]; 5> GO New York -------- NY (1 rows affected)
Adding Strings
As done with numbers, you can add a string to a character or to another string. You can first declare a string variable using char(), nchar(), varchar(), or nvarchar(). You can then use the + operator to add it to another character or string. You can also declare two string variables and add them. Here is an example:
1> DECLARE @first NVARCHAR(12) = N'Gertrude '; 2> DECLARE @last NVARCHAR(12) = N'Monay'; 3> DECLARE @full NVARCHAR(24) = @first + @last; 4> SELECT @first AS [First Name]; 5> SELECT @last AS [Last Name]; 6> SELECT @full AS [Full Name]; 7> GO First Name ------------ Gertrude (1 rows affected) Last Name ------------ Monay (1 rows affected) Full Name ------------------------ Gertrude Monay (1 rows affected)
String Concatenations
String concatenation consists of adding two string. There are various ways to perform this operation. One techniques consists of using the following formula:
CONCAT(value_1, value_2, ..., value_n)
Based on this formula, write CONCAT(). In the parentheses, type eache desired item. Separater them with comas. An item can be a number, a character, a string, or the name of a variable. Here are examples:
1> DECLARE @staffCode numeric = 735608; 2> DECLARE @name nvarchar (25) = N'Paul Bertrand Yamaguchi'; 3> DECLARE @sal decimal = 74668; 4> PRINT CONCAT(N'Employee #: ', @staffCode); 5> PRINT CONCAT(N'Employee Name: ', @name); 6> PRINT CONCAT(N'Yearly Salary: ', @sal); 7> GO Employee #: 735608 Employee Name: Paul Bertrand Yamaguchi Yearly Salary: 74668
Compound Addition on Strings
You can add a string variable to itself. This is done using the SET or the SELECT operator. Here is an example:
1> DECLARE @name NVARCHAR(22) = N'Gertrude'; 2> SET @name = @name + N' '; 3> SET @name = @name + N'Monay'; 4> SELECT @name AS [Full Name]; 5> GO Full Name ---------------------- Gertrude Monay (1 rows affected)
You can also use the composite addition operation is to add one variable to another. To do this, include the += operator between the operands. 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:
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 Operations
The Subtraction
The subtraction operation, is used to take out or subtract one value from another value. The subtraction is performed with the - sign. Here is an example:
PRINT 1240 - 608
Unlike the addition, the subtraction operation is not associative. Consider the following example:
PRINT 128 - 42 - 5 PRINT 5 - 42 - 128
This would produce:
81 -165
The Multiplication
The multiplication allows adding one value to itself a certain number of times. The multiplication is performed with the * sign. Here is an example:
PRINT 128 * 42
This would produce 5376
The Division
The division is used to get the fraction of one number in terms of another. The division is performed with the forward slash /. Here is an example:
PRINT 128 / 42
This would produce 3
When performing the division, be aware of its many rules. Never divide by zero (0).
The Modulo
The modulo operation is used to get the remainder of a division as a natural number. The remainder operation is performed with the percent sign (%). Here is an example:
PRINT 128 % 42
This would produce 2.
Parentheses
Like most computer languages, Transact-SQL uses parentheses to isolate a group of items that must be considered as belonging to one entity. Here is an example:
PRINT (154 - 12) + 8 PRINT 154 - (12 + 8)
This would produce:
150 134
As you can see, using the parentheses controls how the whole operation would proceed. Consider the following example:
SELECT
(SELECT 448.25 * 3) +
(SELECT 82.28 - 36.04);
GO
Bit Manipulations
Introduction
Bit manipulation or a bit related operation allows you to control how values are stored in bits.
Bits Operators: The Bitwise NOT Operator ~
One of the operations you can perform on a bit consists of reversing its value. That is, if a bit holds a value of 1, you may want to change it to 0 and vice-versa. This operation can be taken care of by the bitwise NOT operator that is represented with the tilde symbol ~
The bitwise NOT is a unary operator that must be placed on the left side of its operand as in
~Value
Here is an example:
PRINT ~158
Bits Comparison: The Bitwise AND Operator &
The bitwise & is a binary operator that uses the following syntax
Operand1 & Operand2
This operator considers two values and compares the bit of each with the corresponding bit of the other value. If both corresponding bits are 1, the comparison produces 1. Otherwise, that is, if either bit is 0, the comparison produces 0.
Bits Comparison: The Bitwise OR Operator |
You can perform another type of comparison on bits using the bitwise OR operator that is represented by |. Its syntax is:
Value1 | Value2
Once again, the interpreter compares the corresponding bits of each operand. If at least one of the equivalent bits is 1, the comparison produces 1. The comparison produces 0 only if both bits are 0.
Bits Comparison: The Bitwise-Exclusive XOR Operator ^
Like the previous two operators, the bitwise-exclusive OR operator performs a bit comparison of two values. It syntax is:
Value1 ^ Value2
The interpreter compares the bit of one value to the corresponding bit of the other value. If one of the bits is 0 and the other is 1, the comparison produces 1. In the other two cases, that is, if both bits have the same value, the comparison produces 0. Here is an example:
PRINT 187 ^ 242;
This would produce 73.
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.
Practical Learning: Ending the Lesson
|
|
|||
| Previous | Copyright © 2000-2025, FunctionX | Last Update: Saturday 05 April 2025, 09:11 | Next |
|
|
|||