SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.

If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL.

One SELECT statement can initialize multiple local variables.

Note

A SELECT statement that contains a variable assignment cannot be used to also perform typical result set retrieval operations.

A. Using SELECT @local_variable to return a single value

In the following example, the variable @var1 is assigned Generic Name as its value. The query against the Store table returns no rows because the value specified for CustomerID does not exist in the table. The variable retains the Generic Name value.