Tutorials
Search…
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
Copy link
On this page
DECLARE Syntax
SET Syntax
Examples