DESCENDING/ASCENDING Parameter to a stored procedure

Question

I have the following SP

CREATE PROCEDURE GetAllHouses
    set @webRegionID = 2
    set @sortBy = 'case_no'
    set @sortDirection = 'ASC'

    AS
    BEGIN

        Select 
            tbl_houses.*
        from tbl_houses 
        where 
            postal in (select zipcode from crm_zipcodes where web_region_id = @webRegionID)
        ORDER BY 
            CASE UPPER(@sortBy) 
                    when 'CASE_NO' then case_no 
                    when 'AREA' then area 
                    when 'FURNISHED' then furnished 
                    when 'TYPE' then [type] 
                    when 'SQUAREFEETS' then squarefeets 
                    when 'BEDROOMS' then bedrooms 
                    when 'LIVINGROOMS' then livingrooms 
                    when 'BATHROOMS' then bathrooms 
                    when 'LEASE_FROM' then lease_from 
                    when 'RENT' then rent 
                    else case_no 
            END 
    END
    GO

Now everything in that SP works but I want to be able to choose whether I want to sort ASCENDING or DESCENDING. I really can't fint no solution for that using SQL and can't find anything in google.

As you can see I have the parameter sortDirection and I have tried using it in multiple ways but always with errors... Tried Case Statements, IF statements and so on but it is complicated by the fact that I want to insert a keyword.

Help will be very much appriciated, I have tried must of the things that comes into mind but haven't been able to get it right.

Answer 1

You could use two order by fields:

CASE @sortDir WHEN 'ASC' THEN
    CASE UPPER(@sortBy)
        ...
    END
END ASC,
CASE @sortDir WHEN 'DESC' THEN
    CASE UPPER(@sortBy)
        ...
    END
END DESC

A CASE will evaluate as NULL if none of the WHEN clauses match, so that causes one of the two fields to evaluate to NULL for every row (not affecting the sort order) and the other has the appropriate direction.

One drawback, though, is that you'd need to duplicate your @sortBy CASE statement. You could achieve the same thing using dynamic SQL with sp_executesql and writing a 'ASC' or 'DESC' literal depending on the parameter.

Answer 2

That code is going to get very unmanageable very quickly as you'll need to double nest your CASE WHEN's... one set for the Column to order by, and nested set for whethers it's ASC or DESC

Might be better to consider using Dynamic SQL here...

DECLARE @sql nvarchar(max)
SET @sql = '
        Select 
                tbl_houses.*
        from tbl_houses 
        where 
                postal in (select zipcode from crm_zipcodes where web_region_id = ' + @webRegionID + ') ORDER BY '

SET @sql = @sql + ' ' + @sortBy + ' ' + @sortDirection

EXEC (@sql)