SQL Server: Declare Variables

Learn how to declare variables in SQL Server (Transact-SQL) with syntax and examples.

What is a variable in SQL Server?

In SQL Server (Transact-SQL), a variable allows a programmer to store data temporarily during the execution of code.

Syntax

The syntax to declare variables in SQL Server using the DECLARE statement is:

DECLARE @variable_name datatype [ = initial_value ],
        @variable_name datatype [ = initial_value ],
        ...;

Parameters or Arguments

variable_name

The name to assign to the variable.

datatype

The datatype to assign to the variable.

initial_value

Optional. It is the value initially assigned to the variable when it is declared.

Example – Declare a variable

Let’s look at an example of how to declare a variable in SQL Server.

For example:

DECLARE @techonthenet VARCHAR(50);

This DECLARE statement example would declare a variable called @techonthenet that is a VARCHAR datatype, with a length of 50 characters.

You then change the value of the @techonthenet variable using the SET statement, as follows:

SET @techonthenet = 'Example showing how to declare variable';

Next, let’s also look at how to declare an INT variable in SQL Server.

For example:

DECLARE @site_value INT;

To assign a value to the @site_value variable, you can use the SET statement, as follows:

SET @site_value = 10;

This SET statement would assign the variable @site_value to the integer 10.

Example – Declare more than one variable

Let’s look at how to declare more than one variable at a time in SQL Server.

For example:

DECLARE @techonthenet VARCHAR(50),
        @site_value INT;

In this example, we are declaring two variables. The first is the variable called @techonthenet which is defined as a VARCHAR(50) and the second is a variable called @site_value which is declared as an INT.

Example – Declare a variable with an initial value

Next, let’s look at an example of how to declare a variable in SQL Server and give it an initial value.

For example:

DECLARE @techonthenet VARCHAR(50) = 'Example showing how to declare variable';

This DECLARE statement example would declare a variable called @techonthenet that is a VARCHAR datatype with a length of 50 characters. It would then set the variable of the @techonthenet variable to ‘Example showing how to declare variable’.

Finally, let’s look at how to declare an INT variable in SQL Server and assign an inital value.

For example:

DECLARE @site_value INT = 10;

This variable declaration example would declare a variable called @site_value that is an INT datatype. It would then set the value of the @techonthenet variable to the integer value fo 10.

Example – Declare more than one variable with an initial value

Let’s look at how to declare more than one variable and assign initial values to those variables in SQL Server.

For example:

DECLARE @techonthenet VARCHAR(50) = 'Example showing how to declare variable',
        @site_value INT = 10;

In this example, we are declaring two variables and both of these variables are assigned initial values in their declaration.

• The first variable is called @techonthenet which is defined as a VARCHAR(50) and assigned the initial value of ‘Example showing how to declare variable’.
• The second variable is called @site_value which is declared as an INT and assigned the intial value of 10.