How to set variable from a SQL query?

Question

I'm trying to set a variable from a SQL query:

declare @ModelID uniqueidentifer

Select @ModelID = select modelid from models
where areaid = 'South Coast'

Obviously I'm not doing this right as it doesn't work. Can somebody suggest a solution?

Thanks!

Answer 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