How to set variable from a SQL query?

by

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

More Related Contents:

  1. Calculate a Running Total in SQL Server
  2. How can I do an UPDATE statement with JOIN in SQL Server?
  3. Not equal != operator on NULL
  4. SET NOCOUNT ON usage
  5. How can I get column names from a table in SQL Server?
  6. How can I select the first day of a month in SQL?
  7. Truncate (not round) decimal places in SQL Server
  8. Select statement to find duplicates on certain fields
  9. Is it possible to specify condition in Count()?
  10. Getting only Month and Year from SQL DATE
  11. Dynamic Pivot Columns in SQL Server
  12. WHERE Clause vs ON when using JOIN
  13. How to send email from SQL Server?
  14. How To Create Table with Identity Column
  15. SQL query to find Nth highest salary from a salary table
  16. How to split string using delimiter char using T-SQL?
  17. Calculate the number of records for each date between 2 dates
  18. T-SQL: How to use parameters in dynamic SQL?
  19. Get the records of last month in SQL server
  20. How to compare datetime with only date in SQL Server
  21. I want to use CASE statement to update some records in sql server 2005
  22. SQL server select distinct rows using most recent value only
  23. Access to Result sets from within Stored procedures Transact-SQL SQL Server
  24. alternatives to REPLACE on a text or ntext datatype
  25. Split words with a capital letter in sql
  26. How to return a incremental group number per group in SQL
  27. Eliminate and reduce overlapping date ranges
  28. scope_identity vs ident_current
  29. COALESCE Function in TSQL
  30. Convert bigint to datetime

Leave a Comment