SQL Server 2005 将变量设置为选择查询的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/170078/
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
SQL Server 2005 Setting a variable to the result of a select query
提问by test
How do I set a variable to the result of select query without using a stored procedure?
如何在不使用存储过程的情况下将变量设置为选择查询的结果？
I want to do something like: OOdate DATETIME
我想做类似的事情：OOdate DATETIME
SET OOdate = Select OO.Date
FROM OLAP.OutageHours as OO
WHERE OO.OutageID = 1
Then I want to use OOdate in this query:
然后我想在这个查询中使用 OOdate：
SELECT COUNT(FF.HALID) from Outages.FaultsInOutages as OFIO
INNER join Faults.Faults as FF ON FF.HALID = OFIO.HALID
WHERE CONVERT(VARCHAR(10),OO.Date,126) = CONVERT(VARCHAR(10),FF.FaultDate,126))
AND
OFIO.OutageID = 1
回答by rslite
You can use something like
你可以使用类似的东西
SET @cnt = (SELECT COUNT(*) FROM User)
or
或者
SELECT @cnt = (COUNT(*) FROM User)
For this to work the SELECT must return a single column and a single result and the SELECT statement must be in parenthesis.
为此，SELECT 必须返回单个列和单个结果，并且 SELECT 语句必须在括号中。
Edit: Have you tried something like this?
编辑：你有没有尝试过这样的事情？
DECLARE @OOdate DATETIME
SET @OOdate = Select OO.Date from OLAP.OutageHours as OO where OO.OutageID = 1
Select COUNT(FF.HALID)
from Outages.FaultsInOutages as OFIO
inner join Faults.Faults as FF
ON FF.HALID = OFIO.HALID
WHERE @OODate = FF.FaultDate
AND OFIO.OutageID = 1
回答by Allisson Pereira
-- Sql Server 2005 Management studio
-- Sql Server 2005 管理工作室
use Master
go
DECLARE @MyVar bigint
SET @myvar = (SELECT count(*) FROM spt_values);
SELECT @myvar
Result: 2346 (in my db)
-- Note:@myvar = @Myvar
--注意：@myvar = @Myvar
回答by JPrescottSanders
You could use:
你可以使用：
declare @foo as nvarchar(25)
select @foo = 'bar'
select @foo
回答by Siddhesh Bondre
This will work for original question asked:
这将适用于提出的原始问题：
DECLARE @Result INT;
SELECT @Result = COUNT(*)
FROM TableName
WHERE Condition
回答by Pittsburgh DBA
You could also just put the first SELECT in a subquery. Since most optimizers will fold it into a constant anyway, there should not be a performance hit on this.
您也可以将第一个 SELECT 放在子查询中。由于大多数优化器无论如何都会将其折叠为常量，因此不应该对性能造成影响。
Incidentally, since you are using a predicate like this:
顺便说一句，由于您使用的是这样的谓词：
CONVERT(...) = CONVERT(...)
that predicate expression cannot be optimized properly or use indexes on the columns reference by the CONVERT() function.
该谓词表达式无法正确优化或使用 CONVERT() 函数引用的列上的索引。
Here is one way to make the original query somewhat better:
这是使原始查询更好一些的一种方法：
DECLARE @ooDate datetime
SELECT @ooDate = OO.Date FROM OLAP.OutageHours AS OO where OO.OutageID = 1
SELECT
COUNT(FF.HALID)
FROM
Outages.FaultsInOutages AS OFIO
INNER JOIN Faults.Faults as FF ON
FF.HALID = OFIO.HALID
WHERE
FF.FaultDate >= @ooDate AND
FF.FaultDate < DATEADD(day, 1, @ooDate) AND
OFIO.OutageID = 1
This version could leverage in index that involved FaultDate, and achieves the same goal.
此版本可以利用涉及 FaultDate 的索引，并实现相同的目标。
Here it is, rewritten to use a subquery to avoid the variable declaration and subsequent SELECT.
在这里，重写为使用子查询来避免变量声明和后续的 SELECT。
SELECT
COUNT(FF.HALID)
FROM
Outages.FaultsInOutages AS OFIO
INNER JOIN Faults.Faults as FF ON
FF.HALID = OFIO.HALID
WHERE
CONVERT(varchar(10), FF.FaultDate, 126) = (SELECT CONVERT(varchar(10), OO.Date, 126) FROM OLAP.OutageHours AS OO where OO.OutageID = 1) AND
OFIO.OutageID = 1
Note that this approach has the same index usage issue as the original, because of the use of CONVERT() on FF.FaultDate. This could be remedied by adding the subquery twice, but you would be better served with the variable approach in this case. This last version is only for demonstration.
请注意，此方法与原始方法具有相同的索引使用问题，因为在 FF.FaultDate 上使用了 CONVERT()。这可以通过添加子查询两次来补救，但在这种情况下，您最好使用变量方法。最后一个版本仅用于演示。
Regards.
问候。
回答by Luk
What do you mean exactly? Do you want to reuse the result of your query for an other query?
你是什么意思？您想将您的查询结果重用于其他查询吗？
In that case, why don't you combine both queries, by making the second query search inside the results of the first one (SELECT xxx in (SELECT yyy...)
在这种情况下，为什么不组合两个查询，通过在第一个查询的结果中进行第二个查询搜索(SELECT xxx in (SELECT yyy...)
