CODEWITHSUNDEEP
sqlsorting
abhijith
abhijith

Reputation: 3

Dynamic Order By Clause

I am stuck with Dynamic Order by Clause here in the below query - 

        DECLARE @employee TABLE
        ( 
            ID INT IDENTITY(1,1)
            ,Name VARCHAR(50)
            ,Gender VARCHAR(20)
            ,DOB DATETIME
            ,TotProjects INT
        )


        INSERT INTO @employee
        (   
            Name
            ,Gender 
            ,DOB 
            ,TotProjects
        )
        VALUES 
        ('Mike', 'Male', '07/26/1986', 3)
        ,('Neil' , 'Male', '02/21/1990', 2)
        ,('Jessica' ,'FeMale','04/15/1988', 9)
        ,('Tony','Male','03/18/1987', 6)
        ,('Adam','Male','01/16/1983', 12)


        DECLARE @searchCol VARCHAR(50) = 'PROJECTS' 
        DECLARE @direction VARCHAR(5) = 'ASC'   

        SELECT *
        FROM @employee
        ORDER BY 
                CASE WHEN @direction = 'DESC'
                THEN
                CASE 
                    WHEN @searchCol = 'NAME' THEN Name 
                    WHEN @searchCol = 'GENDER' THEN Gender
                    WHEN @searchCol = 'DOB' THEN CAST (DOB AS VARCHAR(20)) 
                    WHEN @searchCol = 'PROJECTS' THEN ABS(TotProjects)
                END 
            END DESC,
            CASE WHEN @direction = 'ASC'
                THEN
                CASE 
                    WHEN @searchCol = 'NAME' THEN Name 
                    WHEN @searchCol = 'GENDER' THEN Gender
                    WHEN @searchCol = 'DOB' THEN CAST (DOB AS VARCHAR(20)) 
                    WHEN @searchCol = 'PROJECTS' THEN CAST(TotProjects AS VARCHAR(20))
                END
            END ASC



        --- Ends Here -----

So here in the above query i am getting the Order By Direction from one variable called - @direction and i will pass variable - @searchCol to say which of my column is to be sorted.

Now if i pass - "Name" or "Gender" its sorting just fine since both are VARCHAR columns but if i want to sort "Projects" or "DOB" it is not sorting in a correct way. (May be its taking an ascii value) . When i pass direction - @direction - "ASC" and @searchCol - "Projects" it gives weird results. Basically it does not sort based on "Projects" and "DOB" since these are Numeric and Datetime fields respectively.

Ca you please shed some light on this.

Upvotes: 0

Views: 71

Answers (1)

cha
cha

Reputation: 10411

I would implement the dynamic sorting differently. I believe by doing this the database engine will be able to use indexes more effectively (provided you've got them)

 SELECT *
    FROM @employee
    ORDER BY 
    CASE WHEN @direction = 'DESC' AND @searchCol = 'NAME' THEN Name ELSE ' ' END DESC,
    CASE WHEN @direction = 'DESC' AND @searchCol = 'GENDER' THEN Gender ELSE ' ' END DESC,
    CASE WHEN @direction = 'DESC' AND @searchCol = 'DOB' THEN DOB ELSE CAST(0 AS DATE) END DESC,
    CASE WHEN @direction = 'DESC' AND @searchCol = 'PROJECTS' THEN TotProjects ELSE 0 END DESC,
    CASE WHEN @direction = 'ASC' AND @searchCol = 'NAME' THEN Name ELSE ' ' END,
    CASE WHEN @direction = 'ASC' AND @searchCol = 'GENDER' THEN Gender ELSE ' ' END,
    CASE WHEN @direction = 'ASC' AND @searchCol = 'DOB' THEN DOB ELSE CAST(0 AS DATE) END,
    CASE WHEN @direction = 'ASC' AND @searchCol = 'PROJECTS' THEN TotProjects ELSE 0 END

Upvotes: 1

Related Questions