[SOLVED] SQL Server scalar variable holds only last value from table result instead of throwing an error

SQL Server scalar variable holds only last value from table result instead of throwing an error

I have a source table that I think whose table stores only one distinct period. I'm assigning that period to an int variable like below.

SET NOCOUNT ON;
DECLARE @tarih INT;
------------
SELECT @tarih = Dates
FROM
(
    SELECT DISTINCT 
           [Period] AS Dates
    FROM MySourceTable STG WITH(NOLOCK)
) a

But one day I noticed by chance that the table can return more than one value. In this situation, I would expect that SQL Server throws an error. Instead, it takes only last value from table set.

When I change the code like below, it throws an error just as I expect.

DECLARE @tarih INT;
------------
SELECT @tarih = 
(
    SELECT DISTINCT 
           [Period]
    FROM MySourceTable STG WITH(NOLOCK)
)

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

What is the difference between these two syntaxes? Shouldn't the first query also get an error? Do they work differently in the background?

Solution

This behavior is documented in the Variables (Transact-SQL) page:

If a SELECT statement returns more than one row and the variable references a nonscalar expression, the variable is set to the value returned for the expression in the last row of the result set.

The rules for a subquery are different - a subquery can return multiple values if used with set operators such as in, exists, any etc', or with a table alias (as a part of the from or apply clause`.

When a subquery is used as an expression or after a scalar operator, it must return a single scalar value, as specified in the error message you've quoted in the question.
For more information, read the documentation on Subquery types.