How to set variable from a SQL query?

[Originally posted by]: http://stackoverflow.com/questions/3974683/how-to-set-variable-from-a-sql-query

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
shareedit
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s