Home

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.

Variables Fundamentals

Practical LearningPractical Learning: Introducing Variables

  1. Start the computer and log in
  2. Start Microsoft SQL Server and click Connect
  3. On the Standard toolbar, click the New Query button New Query

Declaring a Variable

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:

  • A name can 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 can have combinations of underscores, letters, and digits. Examples are @_n24 or @act_52_t
  • A name cannot include space
  • A name cannot include special characters such as !, @, #, $, %, ^, &, or *
  • If the name is a combination of words, each word will start in uppercase. Examples are @DateHired, @_RealSport, or @DriversLicenseNumber
  • A name should not be a reserved word

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.

Initializing a Variable

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.

Using a Variable

 

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.

Boolean Variables

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:

Declaring a Boolean Variable

Practical LearningPractical Learning: Using Boolean Variables

  1. In the Query Editor, type the following:
    DECLARE @IsMarried bit
    SET @IsMarried = 1
    SELECT @IsMarried AS [Is Married?];
    GO
  2. To execute the statement, press F5

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:

Variables

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:

Variables

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:

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)

Practical LearningPractical 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:
     
    Declaring integer variables

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:

Declaring decimal variables

Practical LearningPractical 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

Declaring a Decimal Variable

Real Numbers

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.

Practical LearningPractical 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
     
    Declaring currency variables

Characters

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)

Strings

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';

Character Variables

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.

Practical LearningPractical Learning: Using String Variables

  1. Change the statement as follows:
    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
  2. Execute the statement
     
    Initializing string variables
  3. Save the file as Variables in your My Documents folder

Text

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 Planar Coordinates

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:

Composite Operations

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:

Composite Operations

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:

Composite Operations

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;

Composite Operations

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:

  • All arithmetic binary operators support the addition, the subtraction, the multiplication, the division, and the remainder binary operators. These are +=, -=, *=, /=, and &= respectively
  • Bit manipulation operations support:
    • The composition bitwise AND operator &= that compares the bits of the left operand to those of the right operand, for equality:
      DECLARE @Number int;
      
      SET @Number = 215;
      SET @Number &= 15;
      SELECT @Number;
    • The composite bitwise OR operator |= that compares the bits of the left operand to those of the right operand, for inequality:
      DECLARE @Number int;
      
      SET @Number = 215;
      SET @Number |= 15;
      SELECT @Number;
    • The composite bitwise-exclusive OR operator ^=
      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 LearningPractical Learning: Ending the Lesson

  1. Close Microsoft SQL Server
  2. If asked whether you want to save the file, click No
 
 
   
 

Previous Copyright © 2000-2013 FunctionX Next