How to set variable from a SQL query?

问题:

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!

回答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

回答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

回答3:

declare @ModelID uniqueidentifer

--make sure to use brackets
set @ModelID = (select modelid from models
where areaid = 'South Coast')

select @ModelID

回答4:

I prefer just setting it from the declare statement

DECLARE @ModelID uniqueidentifer = (SELECT modelid 
                                    FROM models
                                    WHERE areaid = 'South Coast')

回答5:

Use TOP 1 otherwise it could cause error if the query returns multiple rows.

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

回答6:

Select @ModelID =m.modelid 
From   MODELS m
Where  m.areaid = 'South Coast'

In this case if you have two or more results returned then your result is the last record. So be aware of this if you might have two more records returned as you might not see the expected result.