Início / coding / Perguntas / 79435765
Accepted
NiMuSi
Asked: 2025-02-13 18:03:51 +0800 CST

Parâmetro padrão na cláusula where do SQL

  • 772

Estou tentando criar um SQL que selecionará linhas com base na entrada do usuário. Uma das opções será o usuário selecionar todas as linhas.

Considere este exemplo:

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

Os resultados corretos são mostrados quando o usuário seleciona 'M' ou 'F', mas nada é mostrado se o usuário seleciona 'ALL'. Como escrevo o SQL para que a opção 'ALL' mostre todos os registros?

sql

3 respostas

  1. Best Answer

    Eu sugeriria contra uma CASEexpressão, pois a cláusula não seria SARGable e, em vez disso, usaria um OR. Como, no entanto, a declaração sofreria facilmente com sniffing de parâmetros, eu adicionaria um RECOMPILEà OPTIONcláusula.

    Isso resulta no seguinte:

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

  2. Uma solução melhor pode ser usar SQL dinâmico. Certifique-se de passar os parâmetros corretamente. Isso permitirá que o compilador adapte o plano de consulta à consulta.

    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;
    • 1

  3. Adaptei a resposta do @ThomA com o seguinte código (obviamente, este não é código de produção):

    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'

    Isso funciona. Por algum motivo, ele não gostou da consulta CTE_PROMPT sem uma tabela.

    • -1

relate perguntas