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!
This question is related to
sql
sql-server
tsql
There are three approaches:
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
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'
Use TOP 1
if the query returns multiple rows.
SELECT TOP 1 @ModelID = m.modelid
FROM MODELS m
WHERE m.areaid = 'South Coast'
->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;
->DECLARE val1 int;
->DECLARE val2 int;
->SELECT student__id,student_name INTO val1,val2 FROM student_details;
--HAPPY CODING--
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
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.
SELECT
SELECT @ModelID = m.modelid
FROM MODELS m
WHERE m.areaid = 'South Coast'
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.
If this SELECT
statement returns multiple values (bad to begin with):
SELECT
, the variable is assigned the last value that is returned (as womp said), without any error or warning (this may cause logic bugs)SET
, an error will occurI prefer just setting it from the declare statement
DECLARE @ModelID uniqueidentifer = (SELECT modelid
FROM models
WHERE areaid = 'South Coast')
declare @ModelID uniqueidentifer
--make sure to use brackets
set @ModelID = (select modelid from models
where areaid = 'South Coast')
select @ModelID
Source: Stackoverflow.com