我试图从一个SQL查询设置一个变量:
declare @ModelID uniqueidentifer
Select @ModelID = select modelid from models
where areaid = 'South Coast'
很明显,我做得不对,因为它不起作用。谁能提出一个解决方案?
谢谢!
我试图从一个SQL查询设置一个变量:
declare @ModelID uniqueidentifer
Select @ModelID = select modelid from models
where areaid = 'South Coast'
很明显,我做得不对,因为它不起作用。谁能提出一个解决方案?
谢谢!
当前回答
Select @ModelID =m.modelid
From MODELS m
Where m.areaid = 'South Coast'
在这种情况下,如果你有两个或更多的结果返回,那么你的结果是最后的记录。因此,如果您可能会返回另外两条记录,因为您可能看不到预期的结果,请注意这一点。
其他回答
Select @ModelID =m.modelid
From MODELS m
Where m.areaid = 'South Coast'
在这种情况下,如果你有两个或更多的结果返回,那么你的结果是最后的记录。因此,如果您可能会返回另外两条记录,因为您可能看不到预期的结果,请注意这一点。
有三种方法:
声明 SET——微软推荐的方法 选择
下面的查询详细说明了每种方法的优点和缺点:
-- 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
declare @ModelID uniqueidentifer
--make sure to use brackets
set @ModelID = (select modelid from models
where areaid = 'South Coast')
select @ModelID
我更喜欢从declare语句中设置它
DECLARE @ModelID uniqueidentifer = (SELECT modelid
FROM models
WHERE areaid = 'South Coast')
SELECT @ModelID = modelid
FROM Models
WHERE areaid = 'South Coast'
如果您的选择语句返回多个值,则您的变量将被分配返回的最后一个值。
关于对变量使用SELECT的参考:http://msdn.microsoft.com/en-us/library/aa259186%28SQL.80%29.aspx