Lesson 30. Variables

We are now at the point where you understand how to pull data from tables. It is time to start learning how to actually program SQL Server so you can do things like write processes that pull, manipulate, and load data.

Variables only live as long as the script executes. When the script is done, the variable is wiped from memory.

The first step is learning how to create variables and assign them values. You create variables with DECLARE and set their value with SET. When setting your variable, string values have to be enclosed in quotes just like when you were writing WHERE clauses.

DECLARE Syntax

In [ ]:

DECLARE @[VariableName] [DataType]
DECLARE @[VariableName2] [DataType] =  [YourValue]

SET Syntax

In [ ]:

SET @[VariableName] = [YourValue]

Examples

Declaring A Variable

In [ ]:

USE AdventureWorks2016

DECLARE @FirstName NVARCHAR(20)

DECLARE @NumberOfStudents INT

DECLARE @TotalInvioceAmount MONEY

Setting A Variable Value

In [ ]:

USE AdventureWorks2016

DECLARE @FirstName NVARCHAR(20) 

SET @FirstName = 'Bob' 

Declaring A Default Value

In [ ]:

USE AdventureWorks2016

DECLARE @FirstName NVARCHAR(20) = 'Bob'

Viewing The Value Of A Variable

In [ ]:

USE AdventureWorks2016

DECLARE @FirstName NVARCHAR(20) = 'Bob'

PRINT @FirstName ---shows in messages

SELECT @FirstName AS VariableValue --shows in results

Set The Value Of Variables With The Output Of A SQL Statement

The key is the SQL statement has to return a single atomic value.

In [ ]:

USE AdventureWorks2016

DECLARE @FirstName NVARCHAR(20) = (SELECT FirstName FROM Person.Person WHERE BusinessEntityID = 1)

DECLARE @LastName NVARCHAR(20) 

SELECT @LastName = LastName
FROM Person.Person 
WHERE BusinessEntityID = 1

SELECT @FirstName, @LastName

Variable Usage

Variables can be used in various ways. You can use them for output in SELECT statements and in WHERE clauses to create dynamic filter criteria.

In [ ]:

USE AdventureWorks2016

DECLARE @Title NVARCHAR(5) = 'Mr.'
DECLARE @BusinessEntityID TINYINT = 1

SELECT @Title AS Title, FirstName, LastName 
FROM Person.Person 
WHERE BusinessEntityID = @BusinessEntityID