有三种方法:

声明 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语句中设置它

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

要使用SQL选择分配变量，最佳实践如下所示

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

如果你必须在单行中分配多个变量，你可以使用同样的SELECT INTO

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

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

--HAPPY CODING-- 

使用选择

SELECT @ModelID = m.modelid 
  FROM MODELS m
 WHERE m.areaid = 'South Coast'

使用设置

SET @ModelID = (SELECT m.modelid 
                  FROM MODELS m
                 WHERE m.areaid = 'South Coast');

然后，您可以使用SELECT来显示@ModelID的值，或者在代码中使用该变量。

SELECT @ModelID

请参阅此问题了解在TSQL中使用SELECT和SET的区别。

警告

如果SELECT语句返回多个值(开头就不好):

当使用SELECT时，变量被赋给返回的最后一个值(正如womp所说)，没有任何错误或警告(这可能会导致逻辑错误) SET查询只有在查询结尾没有使用分号时才会返回错误

有三种方法:

声明 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