Microsoft SQL Server - Transact-SQL Example: Variables Declarations

	Microsoft SQL Server - Transact-SQL Example: Variables Declarations

Introduction

These are examples of declaring variables.

Introduction

A simple example:

DECLARE @264

Boolean Variables

A Boolean variable is declared with BIT or bit. Here is an example:

DECLARE @IsMarried bit
SET @IsMarried = 1
SELECT @IsMarried AS [Is Married?];
GO

Regular Integers

Here is an example:

DECLARE @Category int;
SET @Category = 1450;
PRINT @Category;
GO

This would produce 1450:

Variables

Small Integers

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)

Long Integers

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)

Decimal and Numeric Types

Here is an example:

1> DECLARE @Distance DECIMAL;
2> SET @Distance = 648.16;
3> PRINT @Distance;
4> GO
648

Here is another example:

Declaring decimal variables

Real Numeric Types

Here is an example:

1> DECLARE @Radius FLOAT;
2> SET @Radius = 48.16;
3> SELECT @Radius AS Radius;
4> GO
Radius
------------------------
      48.159999999999997

(1 rows affected)

Money

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)

Small Money

Heer are examples

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

Declaring currency variables

Character Values

Here is an example:

1> DECLARE @Gender char;
2> SET @GENDER = 'M';
3> SELECT @Gender AS Gender;
4> GO
Gender
------
M

(1 rows affected)

Here is another example:

1> DECLARE @Gender char;
2> SET @GENDER = N'M';
3> SELECT @Gender AS Gender;
4> GO
Gender
------
M

(1 rows affected)

Here is another example:

1> DECLARE @Gender char;
2> SET @Gender = N'Male';
3> SELECT @Gender AS Gender;
4> GO
Gender
------
M

(1 rows affected)

Strings

Here are examples:

1> DECLARE @FirstName    nchar(20),
2>         @LastName     nchar(20);
3> SET     @FirstName =  N'Philomène';
4> SET     @LastName  =  N'Açore';
5> SELECT  @FirstName As "Prénom",
6>         @LastName  As "Nom de Famille";
7> GO
Prénom               Nom de Famille
-------------------- --------------------
Philomène            Açore

(1 rows affected)
1>

Here are other examples:

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

Initializing string variables

Time Values

Here is an example:

1> DECLARE @ArrivalTime time;
2> SET @ArrivalTime = N'18:22';
3> SELECT @ArrivalTime AS [Arrival Time];
4> GO
Arrival Time
----------------
18:22:00.0000000

(1 rows affected)
1>

Date Values

To initialize a DATE variable, use one of the following formulas:

YYYYMMDD
YYYY-MM-DD
MM-DD-YY
MM-DD-YYYY
MM/DD/YY
MM/DD/YYYY

Here is an example:

DECLARE @OneDay DATE;
SET @OneDay = N'10360610';
SELECT @OneDay AS [Day to Prevail];
GO

In US English, this represents October 6th, 1036:

Date

Here is another example:

DECLARE @EventDay date;
SET @EventDay = N'1914-4-7';
SELECT @EventDay AS [Event Day];
GO

In US English, this represents October 6th, 1036

Date

Here are other examples:

DECLARE @SomeDate Date;
SET	@SomeDate = N'5-7-05';
PRINT	@SomeDate;
GO
PRINT N'-----------';
GO
DECLARE @SomeDate Date;
SET	@SomeDate = N'5/7/05';
PRINT	@SomeDate;
GO
PRINT N'-----------';
GO
DECLARE @SomeDate Date;
SET	@SomeDate = N'5-7-41';
PRINT	@SomeDate;
GO
PRINT N'-----------';
GO
DECLARE @SomeDate Date;
SET	@SomeDate = N'5/7/41';
PRINT	@SomeDate;
GO
PRINT N'-----------';
GO
DECLARE @SomeDate Date;
SET	@SomeDate = N'5-7-81';
PRINT	@SomeDate;
GO
PRINT N'-----------';
GO
DECLARE @SomeDate Date;
SET	@SomeDate = N'5/7/81';
PRINT	@SomeDate;
GO
PRINT N'-----------';
GO

Here are examples of results

Date

Here are other examples:

DECLARE @FirstName    nvarchar(20),
        @LastName     nvarchar(20),
        @FullName     nvarchar(40),
        @DateHired    date,
        @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 @DateHired    = N'12/05/1998';
SET @IsMarried    = 1;
SET @EmplStatus   = 2;
SET @WeeklyHours  = 36.50;
SET @HourlySalary = 15.72;
SET @WeeklySalary = @WeeklyHours * @HourlySalary;
SELECT @FullName As [Full Name],
       @DateHired AS [Date Hired],
       @EmplStatus AS [Empl Status],
       @IsMarried AS [Married?],
       @WeeklyHours AS Hours,
       @HourlySalary AS Hourly,
       @WeeklySalary AS Weekly;
GO

Declaring string variables

Combining Date and Time Values

You can use the DATETIME2 data type. To initialize the variable, use one of the following formulas:

YYYYMMDD
YYYYMMDD hh:mm:ss
YYYYMMDD hh:mm:ss[.fractional seconds]
YYYY-MM-DD
YYYY-MM-DD hh:mm:ss
YYYY-MM-DD hh:mm:ss[.fractional seconds]
MM-DD-YY
MM-DD-YY hh:mm:ss
MM-DD-YY hh:mm:ss[.fractional seconds]
MM-DD-YYYY
MM-DD-YYYY hh:mm:ss
MM-DD-YYYY hh:mm:ss[.fractional seconds]
MM/DD/YY
MM/DD/YY hh:mm:ss
MM/DD/YY hh:mm:ss[.fractional seconds]
MM/DD/YYYY
MM/DD/YYYY hh:mm:ss
MM/DD/YYYY hh:mm:ss[.fractional seconds]

Here are examples:

DECLARE @FullName nvarchar(60),
     	@DateOfBirth date,
	@DateRegistered datetime2

SET @FullName       = N'John Summons';
SET @DateOfBirth    = N'19960426';
SET @DateRegistered = N'20090629';
SELECT @FullName AS [Full Name],
       @DateOfBirth AS [Date of Birth],
       @DateRegistered AS [Date Registered];

SET @FullName       = N'James Haans';
SET @DateOfBirth    = N'1994-10-25';
SET @DateRegistered = N'2009-08-02';
SELECT @FullName AS [Full Name],
       @DateOfBirth AS [Date of Birth],
       @DateRegistered AS [Date Registered];

SET @FullName       = N'Gertrude Monay';
SET @DateOfBirth    = N'06-16-92';
SET @DateRegistered = N'2009-12-24 12:36';
SELECT @FullName AS [Full Name],
       @DateOfBirth AS [Date of Birth],
       @DateRegistered AS [Date Registered];

SET @FullName       = N'Philomène Guillon';
SET @DateOfBirth    = N'1996-10-16';
SET @DateRegistered = N'10/14/08 09:42:05.136';
SELECT @FullName AS [Full Name],
       @DateOfBirth AS [Date of Birth],
       @DateRegistered AS [Date Registered];

SET @FullName       = N'Eddie Monsoon';
SET @DateOfBirth    = N'08/10/96';
SET @DateRegistered = N'2009-06-02 12:36';
SELECT @FullName AS [Full Name],
       @DateOfBirth AS [Date of Birth],
       @DateRegistered AS [Date Registered];

SET @FullName       = N'Peter Mukoko';
SET @DateOfBirth    = N'03-10-1994';
SET @DateRegistered = N'7/22/2009 10:24:46.248';
SELECT @FullName AS [Full Name],
       @DateOfBirth AS [Date of Birth],
       @DateRegistered AS [Date Registered];

SET @FullName       = N'Chritian Allen';
SET @DateOfBirth    = N'06/16/1995';
SET @DateRegistered = N'02-09-2009 12:36';
SELECT @FullName AS [Full Name],
       @DateOfBirth AS [Date of Birth],
       @DateRegistered AS [Date Registered];
GO

A SQL Variant

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

User-Defined Types

Here is an example of creating a user-defined type:

CREATE TYPE NaturalNumber FROM int;
GO

Here are examples of creating user-defined types:

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

Here are examples of using user-defined types:

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