Local variables are used in T-SQL scripts for a variety of purposes including:
- Storing values to be tested by control-of-flow statement
- Acting as counter in a loop
- Storing the results of an expression
- Retrieving field values for a single record using a SELECT statement
Variable are also used to pass values into parameters for stored procedures and user-defined functions. When declaring a variable, you must specify its name, datatype and sometimes the length and precision of datatype. The DECLARE statement can be used to declare multiple variables by separating them with commas.
DECLARE @var1 int, @var2 varchar(255);
The preferred way to set the contents of a variable is use the SET statement. It is also possible to use the SELECT statement to set the value of one or more variables.
SELECT @var2='A varchar string';