ROW_NUMBER with Parameter

Question

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.

Answer 1

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)

Answer 2

Try this one -

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

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

PRINT @SQL
EXEC sys.sp_executesql @SQL