[SOLVED] Default parameter in SQL where clause

Default parameter in SQL where clause

I am trying to create SQL that will select rows based on user input. One of the options will be for the user to select all rows.

Consider this sample:

WITH    CTE_PROMPT AS (
        SELECT  'M' Gender
        ),  
    
        CTE_DATA AS (
        SELECT  PERSON_NUMBER
                ,SEX                
        FROM    Employees  
        )
        
SELECT  CTE_DATA.*
FROM    CTE_DATA
        LEFT OUTER JOIN CTE_PROMPT ON CTE_DATA.Sex = CTE_PROMPT.Gender
WHERE   CASE 
            WHEN Gender = 'ALL' THEN 1
            WHEN Gender = SEX THEN 1
            ELSE 0
        END = 1

The correct results are shown when the user selects 'M' or 'F', but nothing is shown if the user selects 'ALL'. How do I write the SQL so that the 'ALL' option shows all records?

Solution

I would suggest against a CASE expression as the clause won't be SARGable and instead use an OR. As, however, the statement would easily suffer from parameter sniffing, I would then add a RECOMPILE to the OPTION clause.

This results in the following:

SELECT E.PERSON_NUMBER, --Why CAPITALS for columns, but Pascal for objects?
       E.SEX                
FROM dbo.Employees E
WHERE E.SEX = @Gender --An actual parameter, not a CTE
   OR @Gender = 'All'
OPTION (RECOMPILE);