CODEWITHSUNDEEP
sql-server-2008dynamicsql-order-by
Rauf
Rauf

Reputation: 12842

Dynamic Query with Order By clause arises an error in SQL Server 2008

I have a dynamic query to execute. To this query, I receive some parameters from user (for example parameters for paging). So I use sp_executesql to execute the query. When I specify order by inside the query, I got an error message says The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.. How can I over come the issue

SET @sql = 'SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY CaseID)AS RowNumber ,*
FROM [dbo].[CV_CaseMaster] WHERE SpaceID = @SID
order by @OrderBy ) AS CV_CaseMasterDetails WHERE RowNumber BETWEEN @MinRow AND @MaxRow'

`

EXEC sp_executesql @sql
                  ,N'@SID UNIQUEIDENTIFIER,@MinRow int, @MaxRow int,@OrderBy VARCHAR(50)'
                  ,@SID=@SpaceID,@MinRow = @CurrentMinRow,@MaxRow = @CurrentMaxRow, @OrderBy =  @SortBy`

OR

Can I specify an Order By column with sp_executesql ?

Upvotes: 1

Views: 979

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

You have put order by in the wrong position in the query and you can not use a parameter for order by.

I guess this will work but it leaves you open for SQL injection.

SET @sql = 'SELECT * 
            FROM (SELECT ROW_NUMBER() OVER(ORDER BY CaseID) AS RowNumber ,*
                  FROM [dbo].[CV_CaseMaster] 
                  WHERE SpaceID = @SID ) AS CV_CaseMasterDetails 
            WHERE RowNumber BETWEEN @MinRow AND @MaxRow 
            ORDER BY '+@SortBy

Upvotes: 2

Related Questions