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:

DECLATE @str_name VARCHAR(100);

SET @str_name = ‘Ateeque’;

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:

/*Declaring a variable*/

DECLARE @str_name varchar(40); 

/* Assining a value to the variable

*/ 

SET @str_name = 'Mike';

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:

/*Declaring variables*/

DECLARE @str_name varchar(40), @int_roll_no int; 

/* Assining a value to the variables

*/ 

SET @int_roll_no = 1001;

SET @str_name = 'Mike';



/*Displaying variable values*/

print @int_roll_no;

print @str_name;

The output in MS SQL Server:

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:

/*Declaring variables*/

DECLARE @emp_name varchar(40); 

/* Assining a value to the variables*/ 

SET @emp_name = 'Mike';



/*Using variable in the query*/

SELECT emp_name, emp_salary, emp_age

FROM sto_employees 

WHERE emp_name = @emp_name;

The result-set:

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

/*Declaring and assigning a value*/

DECLARE @search_emp varchar(40) = 'J%'; 



/*Using variable in the query for searching*/

SELECT emp_name, emp_salary, join_date

FROM sto_employees 

WHERE emp_name LIKE @search_emp;

The result-set:

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:

SELECT * FROM sto_products;



DECLARE @Del_demo varchar(20) = 'Roll_var'; 



BEGIN TRAN @Del_demo 

    DELETE FROM sto_products;

                SELECT * FROM sto_products;                  

ROLLBACK TRAN @Del_demo; 



SELECT * FROM sto_products;

The record-sets before and after:

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