CODEWITHSUNDEEP
sqlsql-server-2008sql-order-bywindow-functionsrow-number
AcAnanth
AcAnanth

Reputation: 775

Windowed functions do not support constants as ORDER BY clause expressions in SQL Server TempTable

This is my dynamic SQL query:

SET @SQL = 'SELECT * FROM 
                (SELECT *, ROW_NUMBER() OVER(ORDER BY '+ @sortBy +') AS rowNumber FROM #temp) A 
            WHERE A.rowNumber BETWEEN ' + CONVERT(varchar(9),
(@startIndex -1) * @PageSize + 1) + ' AND ' + CONVERT(varchar(9), 
(((@startIndex -1) * @PageSize + 1) + @PageSize) - 1)+''

print(@SQL)
exec(@SQL)

And this is the output of the PRINT command:

SELECT * 
FROM
   (SELECT *, ROW_NUMBER() OVER(ORDER BY Typename) AS rowNumber       
    FROM #temp) A 
WHERE A.rowNumber BETWEEN 1 AND 5

where Typename is the @sortBy parameter value.

But I get this error:

Windowed functions do not support constants as ORDER BY clause expressions.

Upvotes: 0

Views: 4720

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35583

Suggest you use QUOTENAME(@sortBy) within the string concatenation, this will ensure the local variable's content s valid as a column name.

Upvotes: 2

Praveen
Praveen

Reputation: 9335

This may not the solution, but if it helps..

Windowed functions do not support constants as ORDER BY clause expressions.

This error normally observed when you try to have a constant value in ORDER BY clause of a windowed function

like ROW_NUMBER() OVER(ORDER BY 'const_val')

Upvotes: 1

Related Questions