431

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!

Tadmas
  • 6,238
  • 3
  • 39
  • 31
Mr Cricket
  • 5,663
  • 5
  • 22
  • 14

9 Answers9

664

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');

Then you can use SELECT to show the value of @ModelID or use the variable into your code.

SELECT @ModelID

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)
  • The SET query returns error only if you DON'T put the semicolon in the end of query
Ronei Kunkel
  • 3
  • 2
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 5
    If this select statement returns multiple values: in the first case, the variable is assigned the last value that is returned (as womp said), without any error or warning (this may cause logic bugs); in the second case, an error will occur. – Francis Niu Oct 20 '10 at 05:01
  • 4
    BTW, the case using SET needs a pair of brackets: SET @ModelID = (SELECT ...) – Francis Niu Oct 20 '10 at 05:03
  • 3
    I would use TOP 1 with select, to have only 1 result, e.g. SET @ModelID = (SELECT TOP 1 m.modelid FROM MODELS m WHERE m.areaid = 'South Coast') – TPAKTOPA May 16 '16 at 11:51
  • In case using set when multiple values are returned then how to handle it using exception handling? – Aasim Jul 19 '18 at 02:47
  • Sometimes you want an error if there's an unexpected duplicate result rather than quietly using an unexpected result. – Denise Skidmore Oct 12 '18 at 17:53
  • Now if SELECT statement returns mutliple value, we will get error `Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.` – Venkataraman R May 27 '20 at 04:51
  • While it's extremally useful it doesn't seem to work with if exists(select @a = a... constructs which is really sad as it forces you to do the same thing twice once to check and another time to fetch the vars without using if exists(). Is there a solution for this problem? – User18165 Feb 25 '22 at 10:32
51
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

womp
  • 115,835
  • 26
  • 236
  • 269
40
declare @ModelID uniqueidentifer

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

select @ModelID
greg121
  • 914
  • 1
  • 11
  • 19
25

I prefer just setting it from the declare statement

DECLARE @ModelID uniqueidentifer = (SELECT modelid 
                                    FROM models
                                    WHERE areaid = 'South Coast')
Joshua Duxbury
  • 4,892
  • 4
  • 32
  • 51
14

Use TOP 1 if the query returns multiple rows.

SELECT TOP 1 @ModelID = m.modelid 
  FROM MODELS m
 WHERE m.areaid = 'South Coast'
manu vijay
  • 367
  • 3
  • 13
  • 1
    It won't actually cause an error in SQL it will select the last record (although it could cause a resulting error in an application if you are using this value and it is incorrect) – d219 Apr 30 '18 at 10:43
11

There are three approaches:

  1. DECLARE
  2. SET -- Microsoft Recommended approach
  3. SELECT

Below query details the advantage and disadvantage of each:

-- First way, 
DECLARE @test int = (SELECT 1)
       , @test2 int = (SELECT a from (values (1),(2)) t(a)) -- throws error

-- advantage: declare and set in the same place
-- Disadvantage: can be used only during declaration. cannot be used later

-- Second way
DECLARE @test int  
       , @test2 int 

SET @test = (select 1)
SET @test2 = (SELECT a from (values (1),(2)) t(a)) -- throws error

-- Advantage: ANSI standard. 
-- Disadvantage: cannot set more than one variable at a time

-- Third way
DECLARE @test int, @test2 int 
SELECT @test = (select 1)
      ,@test2 = (SELECT a from (values (1),(2)) t(a)) -- throws error

-- Advantage: Can set more than one variable at a time
-- Disadvantage: Not ANSI standard
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
9

You can use this, but remember that your query gives 1 result, multiple results will throw the exception.

declare @ModelID uniqueidentifer
Set @ModelID = (select Top(1) modelid from models where areaid = 'South Coast')

Another way:

Select Top(1)@ModelID = modelid from models where areaid = 'South Coast'
Claire
  • 3,146
  • 6
  • 22
  • 37
Pranay_Sharma_Ind
  • 91
  • 1
  • 2
5
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.

d219
  • 2,707
  • 5
  • 31
  • 36
Mohammad Farahani
  • 619
  • 7
  • 12
5

To ASSIGN variables using a SQL select the best practice is as shown below

->DECLARE co_id INT ;
->DECLARE sname VARCHAR(10) ;

->SELECT course_id INTO co_id FROM course_details ;
->SELECT student_name INTO sname FROM course_details;

IF you have to assign more than one variable in a single line you can use this same SELECT INTO

->DECLARE val1 int;
->DECLARE val2 int;

->SELECT student__id,student_name INTO val1,val2 FROM student_details;

--HAPPY CODING--
Venkzz_venki
  • 89
  • 1
  • 3