How to set variable from a SQL query?

Solution 1:

Using SELECT

SELECT @ModelID = m.modelid 
  FROM MODELS m
 WHERE m.areaid = 'South Coast'

Using SET

SET @ModelID = (SELECT m.modelid 
                  FROM MODELS m
                 WHERE m.areaid = 'South Coast')

See this question for the difference between using SELECT and SET in TSQL.

Warning

If this SELECT statement returns multiple values (bad to begin with):

• When using SELECT, the variable is assigned the last value that is returned (as womp said), without any error or warning (this may cause logic bugs)
• When using SET, an error will occur

Solution 2:

SELECT @ModelID = modelid
FROM Models
WHERE areaid = 'South Coast'

If your select statement returns multiple values, your variable is assigned the last value that is returned.

For reference on using SELECT with variables: http://msdn.microsoft.com/en-us/library/aa259186%28SQL.80%29.aspx