Dynamic Order By

  • HI,

    I have the below SP.I want to sort the data according to @Sortby that is passed...

    Here is my Code,

    ALTER PROCEDURE GetInactiveUserdetail

    @PageIndex int,

    @NoOfRows int,

    @SortBy varchar(20),

    @UsersCount int output

    AS

    DECLARE @startRowIndex int;

    BEGIN

    SELECT

    @UsersCount = isnull(count(*), 0)

    FROM

    aspnet_Membership INNER JOIN

    UserActivationDetails ON aspnet_Membership.UserId = UserActivationDetails.UserId INNER JOIN

    UserGeneral ON aspnet_Membership.UserId = UserGeneral.UserId

    WHERE

    (UserActivationDetails.ActivationStatus = 'False') AND (GETDATE() >= aspnet_Membership.CreateDate + 7) AND (aspnet_Membership.UserId NOT IN

    (SELECT UserId

    FROM ActivationReminders))

    SET @startRowIndex = (@PageIndex * @NoOfRows) + 1;

    WITH ContentEntries AS

    (

    SELECT ROW_NUMBER() OVER (ORDER BY m.CreateDate) AS ROW,

    m.UserId, m.Email,m.CreateDate,U.NickName, U.FirstName, U.LastName, U.ImageThumbnailURL, U.Gender, U.RegistrationType,Days=datediff(dd,m.CreateDate, getdate())

    FROM aspnet_Membership m INNER JOIN

    UserActivationDetails a ON m.UserId = a.UserId INNER JOIN

    UserGeneral U ON m.UserId = U.UserId

    WHERE a.ActivationStatus = 'False' AND (GETDATE() >= m.CreateDate + 7) AND (m.UserId NOT IN

    (SELECT UserId

    FROM ActivationReminders))

    )

    SELECT

    UserId, Email, CreateDate, NickName, FirstName, LastName,

    ImageThumbnailURL, Gender, RegistrationType,Days

    FROM

    ContentEntries

    WHERE

    ROW BETWEEN @startRowIndex AND @startRowIndex + @NoOfRows - 1

    ORDER BY

    @SortBy

    END

    But when i am trying to do this there is an Error,I think there is some other way to sort dynamically.. plz help

  • Of course.

    Instead of select....order by @sortby

    you execute this:

    exec('select....order by '+@sortby)

  • You can use a case statement e.g.

    ...

    ORDER BY

    CASE @sortby

    WHEN 'FirstName' THEN u.FirstName

    WHEN 'LastName' THEN u.LastName

    ...

    end

    If you are going to provide the ability to sort on columns that have a different datatype you may end up with an error similar to :-

    Conversion failed when converting the nvarchar value 'LastName' to data type int.

    This is because SQL Server is deciding the datatype of the ORDER BY based on it's rules of precedence, and if it sees that one of the columns could be an integer, it assumes they are all integers. You will then have to explicitly cast all integers, dates etc to varchar e.g.

    ORDER BY

    CASE

    ...

    WHEN 'FirstName' THEN u.FirstName

    WHEN 'UserId' THEN convert(varchar(20),M.UserId)

  • Hi,

    Thanks for the reply, the code below is working now,

    But i have to sort all the datas in the table and then select according to pageIndex,but here first the datas according to the pageindex are fetched and then sorted,

    ALTER PROCEDURE GetInactiveUserdetail

    @PageIndex int,

    @NoOfRows int,

    @SortBy varchar(20),

    @UsersCount int output

    AS

    DECLARE @startRowIndex int;

    BEGIN

    SELECT

    @UsersCount = isnull(count(*), 0)

    FROM

    aspnet_Membership INNER JOIN

    UserActivationDetails ON aspnet_Membership.UserId = UserActivationDetails.UserId INNER JOIN

    UserGeneral ON aspnet_Membership.UserId = UserGeneral.UserId

    WHERE

    (UserActivationDetails.ActivationStatus = 'False') AND (GETDATE() >= aspnet_Membership.CreateDate + 7) AND (aspnet_Membership.UserId NOT IN

    (SELECT UserId

    FROM ActivationReminders))

    SET @startRowIndex = (@PageIndex * @NoOfRows) + 1;

    WITH ContentEntries AS

    (

    SELECT ROW_NUMBER() OVER (ORDER BY m.CreateDate) AS ROW,

    m.UserId, m.Email,m.CreateDate,U.NickName, U.FirstName, U.LastName, U.ImageThumbnailURL, U.Gender, U.RegistrationType,Days=datediff(dd,m.CreateDate, getdate())

    FROM aspnet_Membership m INNER JOIN

    UserActivationDetails a ON m.UserId = a.UserId INNER JOIN

    UserGeneral U ON m.UserId = U.UserId

    WHERE a.ActivationStatus = 'False' AND (GETDATE() >= m.CreateDate + 7) AND (m.UserId NOT IN

    (SELECT UserId

    FROM ActivationReminders))

    )

    SELECT

    UserId, Email, CreateDate, NickName, FirstName, LastName,

    ImageThumbnailURL, Gender, RegistrationType,Days

    FROM

    ContentEntries

    WHERE

    ROW BETWEEN @startRowIndex AND @startRowIndex + @NoOfRows - 1

    ORDER BY

    CASE WHEN @SortBy='Email' THEN Email

    WHEN @SortBy='Days' THEN CONVERT(varchar(50), Days)

    WHEN @SortBy='FrstName' THEN FirstName

    WHEN @SortBy='LstName' THEN LastName

    END

    END

  • Well, this won't work properly, when @SortBy='Days desc' or @SortBy='FrstName, days' or ....

    Also, you're paging on m.CreateDate and sort within page using @SortBy.

    Try something like this:

    EXEC('select .... SELECT ROW_NUMBER() OVER (ORDER BY '+@SortBy+') AS ROW,.... ORDER BY '+@SortBy)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply