Local Variables

Local variables are often used as counters for while loops or if...else blocks in a batch or stored procedure.

When they are used in stored procedures, they are declared for automatic, noninteractive use by the procedure when it executes. You can use variables nearly anywhere the Transact-SQL syntax indicates that an expression can be used, such as char_expr, integer_expression, numeric_expr, or float_expr.

declare @variable_name datatype 
   [, @variable_name datatype]...

The variable name must be preceded by the @ sign and conform to the rules for identifiers. Specify either a user-defined datatype or a system-supplied datatype other than text, image, or sysname.

In terms of memory and performance, this is more efficient:

declare @a int, @b char(20), @c float

than this:

declare @a int
declare @b char(20)
declare @c float

• Local Variables and select Statements
  When you declare a variable, it has the value NULL. Use a select statement to assign values to local variables.
• Local Variables and update Statements
  You can assign variables directly in an update statement. You do not need to use a select statement to assign a value to a variable. When you declare a variable, it has the value NULL.
• Local Variables and Subqueries
  A subquery that assigns a value to the local variable can return only one value.
• Local Variables and while Loops and if…else Blocks
  You can use local variables in a counter in a while loop, for performing matching in a where clause and in an if statement, and for setting and resetting values in select statements.
• Variables and Null Values
  Local variables are assigned the value NULL when they are declared, and may be assigned the null value by a select statement.