SQL 从表中选择一个标量值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4752654/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select a scalar value from a table
提问by WorkerThread
Edit: The problem I had stemmed from a parameter-order mixup in my code. I sincerely appreciate everyone's help; my SQL understanding gets better each visit to SO.
编辑：我的问题源于代码中的参数顺序混淆。衷心感谢大家的帮助；每次访问 SO，我的 SQL 理解都会变得更好。
I am writing a stored procedure which needs to select a bit of information from another table in order to do its job.
我正在编写一个存储过程，它需要从另一个表中选择一些信息才能完成其工作。
DECLARE @configVar int;
SET @configVar = (SELECT ExampleSetting FROM Settings WHERE SettingID = 1);
-- do something with @configVar to get the final result set
Obviously (to people with a better understanding of SQL), the above is incorrect. No error, except when the stored procedure is executed, @configVar is set to NULL. I have double checked the table I'm SELECTing from and have ensured that the data exists.
显然（对于对 SQL 有更好理解的人），以上是不正确的。没有错误，除非在执行存储过程时，@configVar 设置为 NULL。我已经仔细检查了我从中选择的表并确保数据存在。
Can someone show my where my misunderstanding is, and how I should correct it? It seems like this might be a common idiom; how is this normally accomplished?
有人可以告诉我我的误解在哪里，我应该如何纠正它？看起来这可能是一个常见的习语；这通常是如何完成的？
回答by OMG Ponies
TSQL allows you to set variables in the SELECT clause, using:
TSQL 允许您在 SELECT 子句中设置变量，使用：
SELECT @configVar = s.examplesetting
FROM SETTINGS s
WHERE s.settingid = 1
This assumes there's only one record in the table where the settingid value is 1.
这假设表中只有一条记录的 settingid 值为 1。
See question "TSQL - SET vs. SELECT when assigning variables?" for more information on usage.
有关用法的更多信息，请参阅问题“ TSQL - SET 与 SELECT 时分配变量？”。
回答by RichardTheKiwi
Obviously (to people with a better understanding of SQL), the above is incorrect.
显然（对于对 SQL 有更好理解的人），以上是不正确的。
Why would you say that? It is perfectly valid, even ifit can be written as
你怎么会那么说？它是完全有效的，即使它可以写成
SELECT @configVar = ExampleSetting FROM Settings WHERE SettingID = 1;
In fact, it will give you an error in the first form when there are multiple rows returned (try the next query) which lets you know something is not what you expect, whereas the 2nd query above silentlyworks by using the ExampleSetting value from the last matched record.
事实上，它会给你一个错误在第一种形式，当有多个行返回（试了下查询），它可以让你知道的东西是不是你所期望的，而上面的第二个查询默默的工作原理是利用从该ExampleSetting值最后匹配的记录。
DECLARE @configVar int;
SET @configVar = (SELECT number FROM master..spt_values);
-- Msg 512, Level 16, State 1, Line 2
-- Subquery returned more than 1 value.
Run this on its own and you may see something that surprises you
单独运行它，你可能会看到一些让你感到惊讶的东西
SELECT ExampleSetting FROM Settings WHERE SettingID = 1
If this returns no records, that's why SET @configVaris left NULL
如果这不返回任何记录，这就是为什么SET @configVar保留 NULL
FYI, this is what I tried and it worked as advertised
仅供参考，这就是我尝试过的，并且效果如广告所说
DECLARE @configVar int;
SET @configVar = (SELECT top 1 number FROM master..spt_values);
select @configVar;
-- result: -32768
One exception to why you would use the SELECT @varform instead of SET from subqueryform is that the first leaves the variable alone when the query finds no matching rows, the 2nd explicitly sets it to null.
为什么要使用SELECT @var表单而不是SET from subquery表单的一个例外是，当查询没有找到匹配的行时，第一个将变量单独保留，第二个将其显式设置为 null。
DECLARE @configVar int;
SET @configVar = 123;
SET @configVar = (SELECT top 1 number FROM master..spt_values where 1=0);
select @configVar; -- @configVar was set to NULL
SET @configVar = 123;
SELECT top 1 @configVar = number FROM master..spt_values where 1=0;
select @configVar; -- @configVar is LEFT as 123
回答by Conrad Frix
DECLARE @configVar int;
SELECT @configVar = ExampleSetting FROM Settings WHERE SettingID = 1
回答by Matthew
You should do this instead:
你应该这样做：
SELECT @configVar = ExampleSetting FROM Settings WHERE SettingID = 1
Note that if your query returns more than one row your variable will equal the last returned value in the set.
请注意，如果您的查询返回多于一行，您的变量将等于集合中的最后一个返回值。
