动态SELECT TOP @var在SQL Server中

本文翻译自：Dynamic SELECT TOP @var In SQL Server

How can I have a dynamic variable setting the amount of rows to return in SQL Server? 如何设置动态变量来设置SQL Server中要返回的行数？ Below is not valid syntax in SQL Server 2005+: 以下是SQL Server 2005+中无效的语法：

DECLARE @count int
SET @count = 20

SELECT TOP @count * FROM SomeTable

---

#1楼

参考：https://stackoom.com/question/JM6/动态SELECT-TOP-var在SQL-Server中

---

#2楼

SELECT TOP (@count) * FROM SomeTable

这仅适用于SQL 2005+

---

#3楼

The syntax "select top (@var) ..." only works in SQL SERVER 2005+. 语法“select top（@var）...”仅适用于SQL SERVER 2005+。 For SQL 2000, you can do: 对于SQL 2000，您可以执行以下操作：

set rowcount @top

select * from sometable

set rowcount 0 

Hope this helps 希望这可以帮助

Oisin. [287]莪。

(edited to replace @@rowcount with rowcount - thanks augustlights) （编辑用rowcount替换@@ rowcount - 感谢augustlights）

---

#4楼

In x0n's example, it should be: 在x0n的例子中，它应该是：

SET ROWCOUNT @top

SELECT * from sometable

SET ROWCOUNT 0

http://msdn.microsoft.com/en-us/library/ms188774.aspx http://msdn.microsoft.com/en-us/library/ms188774.aspx

---

#5楼

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)

---

#6楼

Or you just put the variable in parenthesis 或者您只需将变量放在括号中

DECLARE @top INT = 10;

SELECT TOP (@Top) *
FROM <table_name>;