CODEWITHSUNDEEP
sqlsql-server-2008
neojakey
neojakey

Reputation: 1663

Pagination ordering with variables in SQL Server

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

Upvotes: 1

Views: 1026

Answers (3)

Conrad Frix
Conrad Frix

Reputation: 52655

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.

Upvotes: 1

Keith
Keith

Reputation: 155702

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.

Upvotes: 1

Marc B
Marc B

Reputation: 360732

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.

Upvotes: 1

Related Questions