DESCENDING/ASCENDING Parameter to a stored procedure

Question

Hi Guys

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)

Answer 3

You could do it with some dynamic SQL and calling it with an EXEC. Beware SQL injection though if the user has any control over the parameters.

CREATE PROCEDURE GetAllHouses
 set @webRegionID = 2 
 set @sortBy = 'case_no'
 set @sortDirection = 'ASC'
 AS
 BEGIN
 DECLARE @dynamicSQL NVARCHAR(MAX)

 SET @dynamicSQL =
 '
 SELECT
  tbl_houses.*
 FROM
  tbl_houses
 WHERE
  postal
 IN
 (
  SELECT
   zipcode
  FROM
   crm_zipcodes
  WHERE
   web_region_id = ' + CONVERT(nvarchar(10), @webRegionID) + '
 )
 ORDER BY
  ' + @sortBy + ' ' + @sortDirection

 EXEC(@dynamicSQL)

 END
 GO