sql-server - 带参数的 ROW_NUMBER

Question

我正在使用 SQL Server 2008。我的程序是

SELECT ContractorID ,
       Name,
       ROW_NUMBER() OVER (ORDER BY ContractorID) as RowNumber
FROM dbo.Contractor 

这是有效的，但现在我想将参数传递到 OVER 函数中。

我已经尝试过:

DECLARE @OrderBy VARCHAR(MAX)
SET @OrderBy = 'ORDER BY ContractorID'

SELECT ContractorID ,
       Name,
       ROW_NUMBER() OVER (@OrderBy) as RowNumber
FROM dbo.Contractor 

这给出了错误“@OrderBy 附近的语法不正确”？我怎样才能做到这一点？谢谢。

Answer 1

你可能有一个动态SQL，然后执行它

DECLARE @OrderBy VARCHAR(MAX)
SET @OrderBy = 'ORDER BY ContractorID'
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 
'SELECT ContractorID ,
       Name,
       ROW_NUMBER() OVER (' + @OrderBy + ') as RowNumber
FROM dbo.Contractor '

EXEC (@SQL)