sql server - Default parameter in SQL where clause - Stack Overflow

admin管理员组

文章数量:1292122

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?

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?

• sql
• sql-server

Share Follow edited Feb 13 at 10:12 Mark Rotteveel 109k229229 gold badges156156 silver badges220220 bronze badges asked Feb 13 at 10:03 NiMuSiNiMuSi 41211 gold badge55 silver badges1616 bronze badges 4

• Your CASE logic, while a bit convoluted, looks correct and should work. – Tim Biegeleisen Commented Feb 13 at 10:06
• A minimal reproducible example is a great start when asking for SQL assistance. – jarlh Commented Feb 13 at 10:25
• There's no user selection in this query. Perhaps there's no ALL to begin with? All this can be replaced with WHERE @thatinput='ALL or @thatinput=SEX, assuming neither SEX nor @thatinput are null. The usual way of writing such catch-all queries is to pass NULL as the wildcard, eg WHERE @thatinput is null or @thatinput=SEX. – Panagiotis Kanavos Commented Feb 13 at 10:29
• Even with this query, the expected clause would be WHERE Gender='ALL' OR Gender=SEX. To handle possible NULL, just add the null checks in the WHERE, eg WHERE Gender='ALL' OR Gender=SEX OR (Gender IS NULL and Sex IS NULL) etc – Panagiotis Kanavos Commented Feb 13 at 10:32

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 1

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);

An better solution might be to use dynamic SQL. Make sure to pass through parameters correctly. This will allow the compiler to tailor the query plan to the query.

DECLARE @Gender char(3) = 'M';

DECLARE @sql nvarchar(max) = N'
SELECT
  e.PERSON_NUMBER,
  e.SEX
FROM dbo.Employees e
';
IF @Gender <> 'ALL'
    SET @sql += N'
WHERE e.SEX = @Gender;
';

PRINT @sql;   -- your friend

EXEC sp_executesql @sql,
  N'@Gender char(3)',
  @Gender;

I've adapted @ThomA answer with the following code (Obviously, this isn't production code):

WITH    CTE_PROMPT AS (
        SELECT  PERSON_NUMBER  
                ,'M' Gender 
        FROM    EMPLOYEES
        )  
        
SELECT  EMPLOYEES.PERSON_NUMBER
        ,SEX                
FROM    EMPLOYEES 
        LEFT OUTER JOIN CTE_PROMPT ON EMPLOYEES.PERSON_NUMBER  = CTE_PROMPT.PERSON_NUMBER
WHERE   SEX = Gender OR Gender = 'ALL'

This works. For some reason it didn't like the CTE_PROMPT query without a table.

本文标签： sql serverDefault parameter in SQL where clauseStack Overflow