CODEWITHSUNDEEP
t-sqlstored-procedures
The real napster
The real napster

Reputation: 2324

DESCENDING/ASCENDING Parameter to a stored procedure

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.

Upvotes: 4

Views: 7200

Answers (3)

Robin Day
Robin Day

Reputation: 102538

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

Upvotes: 0

Eoin Campbell
Eoin Campbell

Reputation: 44308

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)

Upvotes: 1

Tadmas
Tadmas

Reputation: 6368

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.

Upvotes: 9

Related Questions