如何从 SQL 查询设置变量？

How to set variable from a SQL query?

提问by Mr Cricket

I'm trying to set a variable from a SQL query:

我正在尝试从 SQL 查询设置变量：

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!

谢谢！

回答by OMG Ponies

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.

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

Warning

警告

If this select statement returns multiple values(bad to begin with):

如果此 select 语句返回多个值（开头不好）：

• 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

• 使用时SELECT，变量被分配最后一个返回值（如womp所说），没有任何错误或警告（这可能会导致逻辑错误）
• 使用SET时会报错

回答by womp

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.

如果您的 select 语句返回多个值，您的变量将被分配到最后一个返回的值。

For reference on using SELECT with variables: http://msdn.microsoft.com/en-us/library/aa259186%28SQL.80%29.aspx

有关将 SELECT 与变量一起使用的参考：http: //msdn.microsoft.com/en-us/library/aa259186%28SQL.80%29.aspx

回答by greg121

declare @ModelID uniqueidentifer

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

select @ModelID

回答by Joshua Duxbury

I prefer just setting it from the declare statement

我更喜欢从声明语句中设置它

DECLARE @ModelID uniqueidentifer = (SELECT modelid 
                                    FROM models
                                    WHERE areaid = 'South Coast')

回答by manu vijay

Use TOP 1if the query returns multiple rows.

使用TOP 1如果查询返回多行。

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

回答by Pranay_Sharma_Ind

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

您可以使用它，但请记住，您的查询给出 1 个结果，多个结果将引发异常。

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'

回答by Mohammad Farahani

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.

在这种情况下，如果您返回了两个或更多结果，那么您的结果就是最后一条记录。因此，如果您可能没有看到预期的结果，那么如果您可能会返回另外两条记录，请注意这一点。

回答by Venkzz_venki

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

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

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

如果您必须在一行中分配多个变量，您可以使用相同的 SELECT INTO

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

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

--HAPPY CODING-- 

回答by Venkataraman R

There are three approaches:

有以下三种方法：

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

1. 宣布
2. SET-- 微软推荐的方法
3. 选择

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