How to declare variables in SQL?

In SQL, the variable is the way of storing a value temporarily. Various types of variables can be declared and used in SQL databases.

The variables in Transact-SQL are generally used in the batch or stored procedures. The DECLARE statement is used for declaring a variable. For example:

DECLARE @str_name datatype[], @int_num datatype[];

After a variable is declared, this is initialized as NULL.

For assigning a value to a variable, the SET or SELECT statements are used. For example:

You may also assign a value to the variable at the time of declaration. For instance,

DECLARE @str_email VARCHAR(100) = ‘abc@test.com’;

The next section shows you a few running examples of declaring, assigning and using the variables in SQL batch or procedures – so keep reading the rest of this tutorial.

A simple example of declaring and assigning a value

In the first example, we will declare a string variable of the type VARCHAR and assign it a value by using DECLARE statement:

Code:

A variable @str_name is declared and a value is assigned.

The example of printing variable values

In this code snippet, I declared two variables; one of an int type and the other is of varchar. After that, the values are assigned to these variables by SET keyword.

This is followed by displaying the variable values by using print:

The output in MS SQL Server:

SQL DECLARE

You can see, two variables are declared together by using the DECLARE statement.

Using variables in SELECT statement example

In the following example, I have declared a variable, assigned it a value and then used it in the SELECT statement.

In the SELECT statement, the sto_employees table columns are retrieved and the variable is used in the WHERE clause for specifying the employee name to be searched in the table:

The result-set:

SQL DECLARE SELECT

Using a variable in LIKE operator example

A variable is assigned a value at the time of declaration and used in a SELECT query with SQL LIKE operator. The query searches all employees which name begins with letter ‘J’:

The query with a variable in LIKE

The result-set:

SQL DECLARE SEARCH

The example of using a variable for transaction rollback

In the following batch, the DELETE statement is used for removing complete table data. The DELETE command is placed inside the ROLLBACK block; so after removing complete table data, the transaction is rolled-back.

A varchar type variable is used for managing the transaction as follows:

The record-sets before and after:

SQL DECLARE DELETE

The DELETE command removed all records from the table and these are recovered by ROLLBACK.