在 SQL Server 的 select 语句中使用带有 TOP 的变量而不使其动态
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1927450/
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
Use variable with TOP in select statement in SQL Server without making it dynamic
提问by Paresh
declare @top int
set @top = 5
select top @top * from tablename
Is it possible?
是否可以？
Or any idea for such a logic (i don't want to use dynamic query)?
或者对这种逻辑有什么想法（我不想使用动态查询）？
回答by Guffa
Yes, in SQL Server 2005 it's possible to use a variable in the topclause.
是的，在 SQL Server 2005 中，可以在top子句中使用变量。
select top (@top) * from tablename
回答by Espo
SQL Server 2005 actually allows us to parameterize the TOP clause, using a variable, expression or statement. So you can do things like:
SQL Server 2005 实际上允许我们使用变量、表达式或语句来参数化 TOP 子句。因此，您可以执行以下操作：
SELECT TOP (@foo) a FROM table ORDER BY a
SELECT TOP (SELECT COUNT(*) FROM somewhere else) a FROM table ORDER BY a
SELECT TOP (@foo + 5 * 4 / 2) a FROM table ORDER BY a
回答by Brimstedt
In 2005 and later, you can do it as there are several replies in this thread.
在 2005 年及以后，您可以这样做，因为该线程中有多个回复。
Whats less known is that you can achieve this also in 2k, by using SET ROWCOUNT.
鲜为人知的是，您也可以使用 SET ROWCOUNT 在 2k 中实现这一点。
-- Works in all versions
SELECT TOP 10
-- Does not work on 2000
SELECT TOP (10)
SELECT TOP (@rows)
-- Works in both 2ooo and 2oo5
SET ROWCOUNT @max
SELECT *
FROM ...
SET ROWCOUNT 0
Note, if you forget the SET ROWCOUNT 0 at the end, the limit persists.. and you will end up with very hard to locate bugs :-)
请注意，如果您忘记了最后的 SET ROWCOUNT 0，则限制仍然存在..并且您最终将很难找到错误 :-)
