• Robert.Hoffman (11/3/2011)


    Wouldn't the example given also error out if for some reason a Customer had more than one CustomerRefNo returned?

    No. In that case, it would (more or less random (*)) select one of the RefNo values and return that one.

    (*) To be precies, it would process them all, assigning them to the variable in turn, each time overwriting the previous assignment. Only the last assignment would stick, so the value returned would be from the row that happens to be processed last - which, in the absence of an ORDER BY, can be any row.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/