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