SQL Server 中的动态 SELECT TOP @var
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/175962/
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
Dynamic SELECT TOP @var In SQL Server
提问by eddiegroves
How can I have a dynamic variable setting the amount of rows to return in SQL Server? Below is not valid syntax in SQL Server 2005+:
如何使用动态变量设置要在 SQL Server 中返回的行数？以下是 SQL Server 2005+ 中无效的语法：
DECLARE @count int
SET @count = 20
SELECT TOP @count * FROM SomeTable
回答by Brian Kim
SELECT TOP (@count) * FROM SomeTable
This will only work with SQL 2005+
这仅适用于 SQL 2005+
回答by x0n
The syntax "select top (@var) ..." only works in SQL SERVER 2005+. For SQL 2000, you can do:
语法“select top (@var) ...”仅适用于 SQL SERVER 2005+。对于 SQL 2000，您可以执行以下操作：
set rowcount @top
select * from sometable
set rowcount 0
Hope this helps
希望这可以帮助
Oisin.
奥辛。
(edited to replace @@rowcount with rowcount - thanks augustlights)
（编辑以用 rowcount 替换 @@rowcount - 感谢 augustlights）
回答by Codewerks
In x0n's example, it should be:
在 x0n 的例子中，它应该是：
SET ROWCOUNT @top
SELECT * from sometable
SET ROWCOUNT 0
回答by Jan
Its also possible to use dynamic SQL and execute it with the exec command:
也可以使用动态 SQL 并使用 exec 命令执行它：
declare @sql nvarchar(200), @count int
set @count = 10
set @sql = N'select top ' + cast(@count as nvarchar(4)) + ' * from table'
exec (@sql)
回答by ShawnThompson
Or you just put the variable in parenthesis
或者你只是把变量放在括号里
DECLARE @top INT = 10;
SELECT TOP (@Top) *
FROM <table_name>;
回答by David Castro
declare @rows int = 10
select top (@rows) *
from Employees
order by 1 desc -- optional to get the last records using the first column of the table
