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.
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
DECLARE @startdate DATETIME SET @startdate='12/5/2012'
Now if you run this query
DECLARE @startdate DATETIME SET @startdate='12/5/2012' select @startdate
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.