SQL Server - Local Variables

SQL Server - Local Variables

You can create local variables in SQL Server. The basic syntax for creating a local variable is:

DECLARE @variable_name [AS] data_type

All local variables, by default, are initialized as NULL.

A value to a local variable can be assigned in one of the following three ways:

During the declaration of the variable

For example:

Declaring one variable at a time:
DECLARE @var1 [int] = 10

Or

Declaring more than one variable at a time:
DECLARE @var1 [int] = 30,
@var2 [varchar](10) = N'MyVar2';

By using the SET keyword

For example:

DECLARE @var1 [int]
SET @var1 = 10

By using the SELECT statement

For example:

DECLARE @var1 [int]
SELECT @var1 = 30

Below are few examples of how local variables in SQL Server can be used. This is just the starting, you can go a long way from here:

Example 1 of local variables in SQL Server:
A local variable, if not initialized, contains null

DECLARE @var1 [int];
select @var1 as B

Result:

B
------
Null

Example 2 of local variables in SQL Server:
Initialize a local variable, set its value and verify its value

DECLARE @var2 [int];
SET @var2 = 10
select @var2 as A

Result:
A
------
10

Example 3 of local variables in SQL Server:
The text, ntext, and image data types are invalid for local variables:

DECLARE @var1 [text];
select @var1 as B

Result:
-------------------
Msg 2739, Level 16, State 1, Line 2
The text, ntext, and image data types are invalid for local variables.

Example 4 of local variables in SQL Server:
Expressions can be used in local variables:

DECLARE @var2 [int];
SET @var2 = 10+12
select @var2 as A

Result:
    
A
------
22

DECLARE @var2 [int];
SET @var2 = 10+12-2*3
select @var2 as A

Result:

A
------
16

Example 5 of local variables in SQL Server:
Built-in functions can also be used with local variables:

CREATE TABLE tab
(
col int
);

insert into tab values (1)
insert into tab values (2)
insert into tab values (3)

DECLARE @var3 Int
Select @var3 = Max(col)
from tab      
Select @var3

Result:

(No column name)
---------------------------
3

Example 6 of local variables in SQL Server:
Using decision making with local variables:

DECLARE @var int;
SET @var = 750;
IF (@var > 800)
Select 'var is greater than 800'
else
Select 'var is 750'

Result:

(No column name)
--------------------------
var is 750

Example 7 of local variables in SQL Server:
Using compound operators with local variables:

DECLARE @var1 int = 29;
SET @var1 += 2 ;
SELECT @var1 AS Added_Two;

Result:

Added_Two
-----------------
31

Example 8 of local variables in SQL Server:
Concatenating two string and assigning them to a variable:

DECLARE @var1 as varchar (20)
set @var1 = 'Travolta' + ', ' + 'John'
SELECT @var1 AS Name

Result:

Name
-------------
Travolta, John