Pagination ordering with variables in SQL Server

Question

This SQL statement is being flagged in SQL Server in the ORDER BY ' + @SortField + ' ' + @SortOrder + ' part.. Any ideas how can I fix it?

ALTER PROC [sbuser].[sp_MemberMailList ]
@MemberMailID bigint = null,
@FromMemberID bigint = null,
@ToMemberID bigint = null,
@Subject varchar(150) = null,
@Message varchar(8000) = null,
@FromDeletedFlag bit = null,
@ToDeletedFlag bit = null,
@FromArchivedFlag bit = null,
@ToArchivedFlag bit = null,
@ReadFlag bit = null,
@SQL nvarchar(4000) = null,
@SortField varchar(100) = null,
@SortOrder varchar(25) = null,
@NotificationSent bit = null,
@MemberID bigint = null,
@OnHold bit = 0,
@SpecialMail varchar(1) = 'N',
@PageSize float = null,
@PageNum int = 1,
@TotalPages float = null,
@StartDate datetime = null,
@EndDate datetime = null,
@MODE varchar(50)

AS

IF @MODE = 'INBOX-MAIL'
BEGIN
    SELECT @TotalPages = COUNT(*)/@PageSize
    FROM MemberMail a
    INNER JOIN Member b ON b.MemberID = a.FromMemberID
    WHERE a.ToMemberID = @ToMemberID
    AND a.ToDeletedFlag = 0
    AND a.OnHold = 0
    AND a.ToArchivedFlag = 0;

    WITH InMails AS
    (
        SELECT ROW_NUMBER() OVER(ORDER BY ' + @SortField + ' ' + @SortOrder + ') AS RowNum,
        a.MemberMailID,
        a.FromMemberID,
        a.Subject,
        a.CreateDate,
        b.UserName,
        a.ToReadFlag,
        b.Firstname,
        b.Lastname,
        b.MemberDisplayName AS DisplayName
        FROM MemberMail a
        INNER JOIN Member b ON b.MemberID = a.FromMemberID
        WHERE a.ToMemberID = @ToMemberID
        AND a.ToDeletedFlag = 0
        AND a.OnHold = 0
        AND a.ToArchivedFlag = 0
    )
    SELECT * 
    FROM InMails
    WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize
    ORDER BY CreateDate DESC
END

Any help would be appreciated..

Many thanks

Keith · Accepted Answer · 2011-11-01 15:54:40Z

1

You can't specify a field or sort direction with a variable. The entire SQL statement needs to be a dynamic string to do this.

So

SELECT ROW_NUMBER() OVER(
    ORDER BY [field expression] asc|desc) AS RowNum

Is fine, as is

exec('SELECT ROW_NUMBER() OVER(' + 
    'ORDER BY ' + @SortField + ' ' + @SortOrder + ') AS RowNum ...')

But

SELECT ROW_NUMBER() OVER(
    ORDER BY ' + @SortField + ' ' + @SortOrder + ') AS RowNum

Can't be handled by the query compiler in SQL.

answered Nov 1, 2011 at 15:54

Keith

157k82 gold badges314 silver badges453 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

neojakey Over a year ago

Any ideas how I can resolve this? Could really use the help here please.

Keith Over a year ago

@neojakey - either of the first two options will work. Basically you have either static fields in the order by, or the entire statement has to be a dynamic string. This is because what you choose to order by can completely change the query plan and how it's optimised, so there's no value to SQL in caching it.

Keith Over a year ago

@neojakey - there is one way to get round that, but it's nasty. Your sproc can only have sort fields that are in your MemberMail or Member tables, so you could repeat the entire statement for each sortable field. That'll be very ugly code, but will give SQL query plans that it can cache.

neojakey Over a year ago

I repeated the entire statement 2 times for ASC and DESC. Many thanks for your assistance..!

Marc B · Accepted Answer · 2011-11-01 15:49:32Z

1

SELECT ROW_NUMBER() OVER(ORDER BY ' + @SortField + '   ' + @SortOrder + ') AS RowNum,
                                  ^^^^^^^^^^^^^^^^^^   ^^^^^^^^^^^^^^^^^^
                                    string #1              string #2

                                                      ^---no operator

Unless you've got an outer string not shown in your code sample, you cannot build up an order by clause like this. You've got two different quoted strings with a space between then.

answered Nov 1, 2011 at 15:49

Marc B

362k44 gold badges433 silver badges508 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:47:58Z

1

It looks like you're trying to dynamically changing the fields ORDER BY the ROW_NUMBER() ranking function. You can't do it the way you're trying.

You need to use dynamic SQL to accomplish what you're trying. You should read The Curse and Blessings of Dynamic SQL to get a good understanding of the mechanics as well as how to protect yourself from SQL Injection.

If for some reason you don't want to use dynamic SQL you can generate a giant case statement. For example this sample below taken from gbn's answer to Dynamic order direction.

SELECT
     Columns you actually want
FROM
    (
    SELECT
         Columns you actually want,
         ROW_NUMBER() OVER (ORDER BY AddedDate) AS AddedDateSort,
         ROW_NUMBER() OVER (ORDER BY Visible) AS VisibleSort,
         ROW_NUMBER() OVER (ORDER BY AddedBy) AS AddedBySort,
         ROW_NUMBER() OVER (ORDER BY Title) AS TitleSort
    FROM
         myTable
    WHERE
         MyFilters...
    ) foo
ORDER BY
     CASE @OrderByColumn
        WHEN 'AddedDate' THEN AddedDateSort
        WHEN 'Visible' THEN VisibleSort    
        WHEN 'AddedBy' THEN AddedBySort
        WHEN 'Title' THEN TitleSort
     END * @multiplier;

If you do that you should be on the lookout for performance issues since indexes may not be used for the sorting.

edited May 23, 2017 at 11:47

CommunityBot

11 silver badge

answered Nov 1, 2011 at 15:57

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

1 Comment

Keith Over a year ago

Nice solution - you could simplify it further with a sub query where the case switch is a selection. Then you have the row_number() function across that.

Collectives™ on Stack Overflow

Pagination ordering with variables in SQL Server

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related