Defining Variables

Defining Variables in SQL Server:

Like other programming languages T-SQL allows to defining your variables. A variable is known as local variable due to scope of variable. A variable can be defined with keyword ‘DECLARE’ .A variable can be declared as any System or User Defined data type. There are some rules to define variables in SQL Server

·         Variable name must be start with ‘@’.

·         Embedded spaces or special characters are not allowed.

·    Local variable names must conform to the rules for identifiers. For more information, see Using Identifiers.

For example:

DECLARE @startdate DATETIME;

              

When you declare a variable, it is initialized as NULL, unless a value is provided as part of the declaration. In above case

If you run this

DECLARE @startdate DATETIME
Select @startdate

                

Output:

You can use SET keyword as

DECLARE @startdate DATETIME
SET @startdate='12/5/2012'

            

Now if you run this query

DECLARE @startdate DATETIME
SET @startdate='12/5/2012'
select @startdate

Output:

You can define more than variable with single DECLARE statement. You need to separate each variable definition with comma as

DECLARE @startdate DATETIME,@enddate DATETIME,@name VARCHAR(200),@dob DATETIME

Another approach that can be following to define ad initialize variables together is as

DECLARE @name  varchar(100) = 'Queryingsql'

Remarks:

Variables are often used in a batch or procedure as counters for WHILE, LOOP, or for an IF...ELSE block.

Variables can be used only in expressions, not in place of object names or keywords. To construct dynamic SQL statements, use EXECUTE.

The scope of a local variable is the batch in which it is declared.


Prev--->Defining Variables in SQL server                                                      Next--->Data Types in SQL Server