Basically – there are two ways (at least?) of selecting information into a variable from a table/select query.

You can either SELECT @Variable = COLUMN FROM TABLE or you can SET @Variable = (SELECT COLUMN FROM TABLE).

There is one big difference between the two one needs to be aware of. The first method will not set a new value to @Variable if there isn’t returned a row from the select statement – but the second one will.

This is easy to see by running a query like this where the SELECT will return no rows:

DECLARE @Value INT
DECLARE @i INT
SET @i = 0

WHILE @i < 100 BEGIN
   SELECT @Value = MyID FROM Table_1 WHERE MyValue IS NULL
   IF @Value IS NULL 
           SET @Value = @i
   PRINT(@Value)
   SET @i = @i + 1
END 

SET @i = 0
SET @Value = 0

Now the first impression will be that the values 0 to 99 will be printed out, but that is wrong. You’ll get 100 prints of 0. That is because the SELECT doesn’t return any rows so the assignment doesn’t happen, because it occurs within the SELECT statement.

Whereas if you change the query to this:

WHILE @i < 100 BEGIN
   SET @Value = (SELECT MyID FROM Table_1 WHERE MyValue IS NULL)
   IF @Value IS NULL 
           SET @Value = @i
   PRINT(@Value)
   SET @i = @i + 1
END 

You’ll get the expected assignment of 0 to 99 when run, because the SELECT doesn't return a row, the SET will assign NULL. A big difference in the execution of the assignment.