Microsoft SQL Server 2012 Bible

Working with Variables

Every language requires variables to temporarily store values in memory. T-SQL variables are created with the DECLARE command. The DECLARE command is followed by the variable name and data type. The available data types are similar to those used to create tables, with the addition of the table and cursor. The deprecated text, ntext, and image data types are only available for table columns, and not for variables. Multiple comma-separated variables can be declared with a single DECLARE command.

Variable Default and Scope

The scope, or application and duration, of the variable extends only to the current batch. Newly declared variables default to NULL and must be initialized if you want them to have a value in an expression. Remember that NULL added to a value yields NULL.

The following script creates two test variables and demonstrates their initial value and scope. The entire script is a single execution, even though it's technically two batches (separated by a GO), so the results of the three SELECT statements appear at the conclusion of the script:

DECLARE @Test INT ,
     @TestTwo NVARCHAR(25);
SELECT @Test, @TestTwo;

SET @Test = 1;
SET @TestTwo = ‘a value';
SELECT @Test, @TestTwo ;

GO

SELECT @Test AS BatchTwo, @TestTwo;

Result of the entire script:

----------- ------------------------- NULL NULL (1 row(s) affected) ----------- ------------------------- 1 a value (1 row(s) affected) Msg 137, Level 15, State 2, Line 2 Must declare the scalar variable "@Test". ...