Assigning a variable from a select statement

Question

What is the correct syntax?

DECLARE @TotalRows INT SET @TotalRows = (SELECT COUNT(*) FROM MyTable WHERE MyID = Value)

OR

 DECLARE @TotalRows INT SELECT @TotalRows = COUNT(*) FROM MyTable WHERE MyID = Value

Does it even matter?

+4

Maxim Gershkovich Jun 06 '11 at 5:14

1 answer

Pranay rana · Accepted Answer · 2011-06-06T05:16:30+0000

SET is the ANSI standard for assigning variables, SELECT is not.
SET can assign only one variable at a time, SELECT can do several assignments at once.
When assigned from a query, SET can only assign a scalar value. If the query returns multiple values / rows, then SET raises an error. SELECT will assign one of the values to the variable and hide the fact that several values were returned (so you probably never know why something went wrong elsewhere) have fun troubleshooting this)
When assigning from a query, if there is no return value, SET will assign NULL, where SELECT will not perform the assignment at all (therefore, the variable will not be changed from its previous value)
As for the speed difference - there are no direct and direct lines between the settings and SELECT. However, SELECT's ability to perform multiple assignments in one shot gives it a slight advantage over SET.