CODEWITHSUNDEEP
sql-servert-sql
Philip
Philip

Reputation: 2628

ORDER BY, SELECT statement, Duplicate Code

I have the below stored procedure and depending on the @AreaNumber parameter, it will run the exact same query but with a different order by clause.

Is there a way I can combine the two queries, rather than having the IF statement?

ALTER PROCEDURE [dbo].[spAlerts]
    @AreaNumber tinyint = NULL

IF @AreaNumber is NULL
    SELECT *
    FROM alerts
    ORDER BY DateModified DESC, DateCreated DESC
ELSE
    SELECT * 
    FROM alerts
    ORDER BY IsPermanent ASC, DateModified DESC, DateCreated DESC

Upvotes: 1

Views: 61

Answers (2)

iSR5
iSR5

Reputation: 3498

Well, you could do it using CASE either in ORDER BY clause or using ROW_NUMBER(). 

SELECT *
FROM alerts
ORDER BY 
    CASE WHEN @AreaNumber IS NOT NULL THEN IsPermanent ELSE '' END
,   DateModified DESC
,   DateCreated DESC

OR 

SELECT *
,   CASE WHEN @AreaNumber IS NOT NULL 
    THEN ROW_NUMBER() OVER(ORDER BY IsPermanent ASC, DateModified DESC, DateCreated DESC)
    ELSE ROW_NUMBER() OVER(ORDER BY DateModified DESC, DateCreated DESC)
    END RN  
FROM alerts

Using ROW_NUMBER() will add an extra column (row number), so maybe making it a sub-query will be a better idea. However, I just want to give you more options as it might help out.

Upvotes: 1

dtanabe
dtanabe

Reputation: 1661

ORDER BY can be an arbitrary expression, so you could move your IF inside the query itself (but as a CASE expression instead):

select * from alerts
    ORDER BY (CASE WHEN @AreaNumber IS NULL THEN '-' ELSE IsPermanent END) ASC,
             DateModified DESC,
             DateCreated DESC

I don't have a SQL Server instance handy to test for quirks, but the '-' is essentially meant to be a dummy stand in. It will have the same value for all rows, so therefore won't affect sorting.

Upvotes: 3

Related Questions