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.

Accepted Answer

```
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.

Set A Variable From A Table

Solution

Problem