How to set variable from a SQL query?

Question

Hi, 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

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

Answer 2

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