SQL 如何在存储过程中执行 SELECT TOP @Param？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/779149/
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
How to do SELECT TOP @Param in a Stored Procedure?
提问by Chris Thornhill
I'm trying to do the following in a proc, but getting an incorrect syntax error:
我正在尝试在 proc 中执行以下操作，但收到不正确的语法错误：
SELECT TOP @NumberOfResultsToReturn *
What am I doing wrong here? Thanks.
我在这里做错了什么？谢谢。
回答by Chris Thornhill
Add parenthesis:
添加括号：
SELECT TOP (@NumberOfResultsToReturn) *
回答by n8wrl
SQL Server: Put the argument in parens:
SQL Server：将参数放在括号中：
SELECT TOP (@NumberOfResultsToReturn) *
回答by galets
Here's how I was doing it in the old times:
在过去，我是这样做的：
SET @@ROWCOUNT = @NumberOfResultsToReturn
SELECT ........
SET @@ROWCOUNT = 0
This will work, although SELECT TOP (@NumberOfResultsToReturn)is preferable if you're using SQL server that supports this syntax:
这将起作用，但SELECT TOP (@NumberOfResultsToReturn)如果您使用支持此语法的 SQL 服务器则更可取：
回答by Joel Coehoorn
This is supported in SQL Server 2005 and later, but not in SQL Server 2000. What version are you using?
这在 SQL Server 2005 及更高版本中受支持，但在 SQL Server 2000 中不受支持。您使用的是什么版本？
回答by TheTXI
You may have to use the RowNumber() method instead.
您可能必须改用 RowNumber() 方法。
Here is an example:
下面是一个例子：
DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;
WITH OrdersRN AS
(
SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
,OrderID
,OrderDate
,CustomerID
,EmployeeID
FROM dbo.Orders
)
SELECT *
FROM OrdersRN
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize
ORDER BY OrderDate
,OrderID;
EDITOr you could use parentheses...which I was unaware of at the time :) Thanks guys.
编辑或者你可以使用括号......我当时不知道:) 谢谢大家。
回答by Jhonny D. Cano -Leftware-
I'm afraid you can't do this in SQL 2000, but you can try to construct the query
恐怕你不能在 SQL 2000 中做到这一点，但你可以尝试构造查询
DECLARE @query VARCHAR(500)
set @query = 'SELECT TOP ' + @NumberOfResultsToReturn + '* FROM table'
EXEC @query
I didn't know the parenthesis trick for SQL 2005, thanks too guys, !!!
我不知道 SQL 2005 的括号技巧，也谢谢大家，！！！
