Working with Variables in TSQL Programming

Variable:

A variable is nothing but a memory location whch can hold a value inside it.At any point of time we can have one value in the varibale.

We can use that varibale's value as part of our program logic implementation and we can override the value with new value if required.

We have two types of variables,those are

1)Local variables: These are declared by developers the scope of the varibale is withing that block of code.It will be preceding with single @

2)Global Variables:These are already declared by SQL SERVER,developers can use these variables,the scope of the variables are Global. These varibales can identifyable with @@
  
Ex: @@Fetch_status

Working with Local variables:

Declaring a variable in SQL Server:

Syntax:

DECLARE @<variable_name> datatype[size]

Example:

DECLARE @a int
DECLARE @name varchar(20)
DECLARE @tdate Date

We can delcare multiple variables in a single statement as follows

DECLARE @a int
                    ,@name varchar(20)
                    ,@tdate Date

2)How to Assign a value to a variable

To assign a static value:

   SET @<ariable_name>=value/expression

   SET @a=100
   SET @tdate=GETDATE( )
   SET  @name='SRINIVAS'

To assign a values from table:

We can bring the values from table and assign it a variables by using the below approach

   SELECT @variable_name1=col1(col1_value),@variable_name2=col2(col2_value)
   from <remaining SELECT statement>

 Example:

 DECLARE @tename varchar(20)
 DECLARE @tesal int
 DECLARE @teno int
 SET @teno=1003
 SELECT @tename=ename,@tesal=esal from employee where eno=@teno

3)Printing a value:

From program we can print anything with the help of print statment.

PRINT <statement>

PRINT 'srinivas'

Example 1:

DECLARE @a int
DECLARE @b int
SET @a=100
SET @b=200
PRINT @a+@b

We can optimise the above statement as bellow ways

Method 1:

DECLARE @a int ,@b int
SET @a=100
SET @b=200
PRINT @a+@b

Method 2:
DECLARE @a int=100
DECLARE @b int=200
PRINT @a+@b

Method 3:

DECLARE @a int=100,
                   @b int=200
PRINT @a+@b

All the above four statements are correct and represents the same.

Example 2

Write a Query which will print the empname and salary of a given number.

DECLARE @tename varchar(20)
DECLARE @tesal int
DECLARE @teno int
SET @teno=1003
SELECT @tename=ename,@tesal=esal from employee where eno=@teno
PRINT @tename
PRINT @tesal

Way 2:

DECLARE @tename varchar(20)
                   ,@tesal int
                  ,@teno int=1003
SELECT @tename=ename,@tesal=esal from employee where eno=@teno
PRINT @tename
PRINT @tesal

Let me know if you need any more information on this.

Thanks
SQL SRINIVAS