SQL Server Set A Variable From A Table

Question

If I want to set a variable to a field in a table I normally use something like

SELECT @UserIdToUpdate = userId FROM #NewUsers

In this case there will be multiple results and I just want the first one so I tried this but it fails and says invalid syntax top

SELECT @UserIdToUpdate = TOP 1 UserId FROM #NewUsers

If this is this case can I just usethe first example without the top? I assume it will just take the first record? I know it seems like on odd thing to do but the command is in a loop so it will select a record, do something with it, delete it then select the next one.

Answer 1

This should work

SELECT @UserIdToUpdate = userId FROM #NewUsers LIMIT 1

Answer 2

SELECT @UserIdToUpdate = NULL
SELECT TOP 1 @UserIdToUpdate = userId FROM #NewUsers

The first statement is needed because if the second finds zero rows, then the variable will not get assigned at all and will keep its prior value.

Alternatively,

SELECT @UserIdToUpdate = (SELECT TOP 1 userId FROM #NewUsers)

this will work even if zero rows are found.

Answer 3

SELECT @UserIdToUpdate = (SELECT TOP 1 UserId FROM #NewUsers)

But I believe your first query will work as well, if you then delete this row. And SQL Server should not read all the rows, but actually should select only the first one (in arbitrary order)

Answer 4

SELECT TOP 1 @UserIdToUpdate = UserId FROM #NewUsers