Search code examples
sqlsql-serversqlparameter

What value is selected into parameter in SQL query without where clause

For example, I have this query

SELECT @param = column from table

What value is pulled into @param?

I tried this and can't figure out the value that is being pulled. It is not the old record or newer one.

Solution

  • The documentation states:

    the variable is assigned the last value that is returned

    But without a WHERE clause that uniquely identifies a row nor an ORDER BY clause that specifies a unique value for ordering, the row chosen for the variable assignment is undefined and not deterministic when the table has more than one row.

    You could add ORDER BY to the query to return the last ordered row. A more efficient method to do that would to be use SELECT TOP(1)...ORDER BY...DESC. Conversely, SELECT TOP(1)...ORDER BY...ASC will return the first ordered row. Again, the order by column(s) need to be unique for a deterministic value.