Variables

Sometimes you’ll need to hold a value or work with a value that does not come directly from a column. Or perhaps you’ll need to retrieve a value from a single row of data and a single column that you want to use in a different part of a query. It is possible to do this via a variable.

You can declare a variable at any time within a set of T-SQL instructions, whether it is ad hoc or a stored procedure or trigger. However, a variable has a finite lifetime.

To inform SQL Server that you want to use a variable, use the following syntax:

All variables have to be preceded with an @ sign, and as you can see from the syntax, more than one variable can be declared, although multiple variables must be separated by a comma or a new line with a new DECLARE statement. Code can be held on more than one line within the editor, just like all other T-SQL syntax, such as having DECLARE on line 1 and then the variables on line 2. All variables can hold a NULL value, and there is not an option to say that the variable cannot hold a NULL value. By default, then, when a variable is declared, it will have an initial value of NULL. It is also possible to assign a value at the time of declaration. You’ll see this in the first set of the following code. To assign a value to a variable, you can use a SET statement or a SELECT statement. It is standard to use SET to set a variable value when you are not working with any tables, and it is useful when you want to set the values of multiple variables at the same time. It is also useful when you want to check the system variables @@ERROR or @@ROWCOUNT following some other DML statements. You will see these system variables later within this chapter. Let’s take a look at some examples to see more of how to work with variables and their lifetime.