ROW_NUMBER with Parameter

sql-server, sql-server-2008

Solution

You may have a dynamic SQL and then execute it

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)

Problem

I am using SQL Server 2008. My procedure is ``` SELECT ContractorID , Name, ROW_NUMBER() OVER (ORDER BY ContractorID) as RowNumber FROM dbo.Contractor ``` This is working but now I want to pass parameter into the `OVER` function. I have tried : ``` DECLARE @OrderBy VARCHAR(MAX) SET @OrderBy = 'ORDER BY ContractorID' SELECT ContractorID , Name, ROW_NUMBER() OVER (@OrderBy) as RowNumber FROM dbo.Contractor ``` this gives the error 'incorrect syntax near @OrderBy'? How can I do this? Thanks.

Original source