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.