Início / dba / Perguntas / 345003
Accepted
Jefferson
Asked: 2025-01-30 21:22:07 +0800 CST

Procedimento armazenado para obter o startDate máximo com AgentId opcional

  • 772

Tenho esse procedimento armazenado que quero a base max startDate no AgentId ou sem o AgentId. A maneira como estou fazendo isso é usando um if else e queria ver se havia uma maneira melhor de fazer isso?

  IF (@AgentId = 0)
      BEGIN
          SELECT top (1)max(cgv.StartDate) as AgentLatestPublishedDate,ag.Name
          FROM compendia.Agent ag
          JOIN compendia.DrugCompendium AS dc ON dc.AgentId = ag.OriginalAgentId 
          JOIN compendia.CompendiaGuidelineVersion cgv ON cgv.CompendiaGuidelineVersionId = dc.CompendiaGuidelineVersionId
          JOIN guideline.Disease AS d ON d.DiseaseId = dc.DiseaseId
          WHERE cgv.WorkFlowStatusId = 6 AND ag.EndDate IS NULL AND dc.IsNoLongerRecommended = 0
          group by ag.AgentId,ag.Name
      end

    ELSE
    BEGIN
          SELECT max(cgv.StartDate) as AgentLatestPublishedDate,ag.Name
          FROM compendia.Agent ag
          JOIN compendia.DrugCompendium AS dc ON dc.AgentId = ag.OriginalAgentId 
          JOIN compendia.CompendiaGuidelineVersion cgv ON cgv.CompendiaGuidelineVersionId = dc.CompendiaGuidelineVersionId
          JOIN guideline.Disease AS d ON d.DiseaseId = dc.DiseaseId
          WHERE cgv.WorkFlowStatusId = 6 AND ag.EndDate IS NULL AND dc.IsNoLongerRecommended = 0 and ag.AgentId = @AgentId
          group by ag.AgentId,ag.Name
    end
sql-server

3 respostas

  1. Best Answer

    Solução atual

    Sua versão certamente não é a pior. O problema principal é que você tem o mesmo código duas vezes, o que tem alguma sobrecarga de manutenção (ou seja, todas as alterações devem ser aplicadas a ambos os blocos de código).

    Mas T-SQL não é Java ou C# - ele não fornece tantas ferramentas para deduplicação de código (e muitas das ferramentas de deduplicação que ele fornece tornarão as coisas mais difíceis para o otimizador). Programadores de aplicativos podem dizer Não se repita , mas como programador T-SQL, às vezes você simplesmente tem que fazer isso.

    Alguém poderia argumentar razoavelmente que é melhor deixar as coisas como estão.

    Solução ingênua

    Você pode usar um bloco único com um filtro ligeiramente diferente: ... and (ag.AgentId = @AgentId OR @AgentId = 0). Mas o problema é que isso ORtorna as coisas mais difíceis para o otimizador. O SQL Server cria um plano de execução para a consulta uma vez e, em seguida, o reutiliza para execuções subsequentes (até que o plano seja empurrado para fora da memória). Isso é bom, porque economiza tempo em compilações. No entanto, também significa que ele sempre tem que verificar se ag.AgentId = @AgentId, porque ele não sabe com antecedência qual valor você passará para o parâmetro

    Ótimo para pequenos conjuntos de dados, caso contrário, evite.

    Solução dinâmica

    Isso facilitará as coisas para o otimizador, e você não terá o mesmo código duas vezes. Ele depende de SQL dinâmico para ter apenas uma cópia única do seu código SQL, e o altera para incluir ou pular a @AgentIdverificação.

    DECLARE @sql NVARCHAR(MAX) = N'
SELECT max(cgv.StartDate) as AgentLatestPublishedDate,ag.Name
FROM compendia.Agent ag
JOIN compendia.DrugCompendium AS dc ON dc.AgentId = ag.OriginalAgentId 
JOIN compendia.CompendiaGuidelineVersion cgv ON cgv.CompendiaGuidelineVersionId = dc.CompendiaGuidelineVersionId
JOIN guideline.Disease AS d ON d.DiseaseId = dc.DiseaseId
WHERE cgv.WorkFlowStatusId = 6 
  AND ag.EndDate IS NULL 
  AND dc.IsNoLongerRecommended = 0 
  <<AGENT_FILTER_HERE>>  /*Notice the placeholder here*/
group by ag.AgentId,ag.Name
';

/*Now you either remove the placeholder, or replace it with actual condition.*/
IF @AgentId = 0
  SET @sql = REPLACE(@sql, N'<<AGENT_FILTER_HERE>>', N'');
ELSE
  SET @sql = REPLACE(@sql, N'<<AGENT_FILTER_HERE>>', N'and ag.AgentId = @AgentId');

/*Executing the parametrized dynamic SQL.*/
EXECUTE sp_executesql @sql, N'@AgentId INT', @AgentId = @AgentId;
    • 4

  2. Se você realmente deseja evitar repetições (DRY) e não deseja usar SQL dinâmico, você pode usar uma função de tabela com valor embutida.

    O TOP (1)no segundo ramo parece estranho.

    CREATE OR ALTER FUNCTION dbo.GetAgentInfo (@HasAgentFilter bit, @AgentId bigint)
RETURNS TABLE
AS RETURN
    SELECT
      MAX(cgv.StartDate) as AgentLatestPublishedDate,
      ag.Name
    FROM compendia.Agent ag
    JOIN compendia.DrugCompendium AS dc ON dc.AgentId = ag.OriginalAgentId 
    JOIN compendia.CompendiaGuidelineVersion cgv ON cgv.CompendiaGuidelineVersionId = dc.CompendiaGuidelineVersionId
    JOIN guideline.Disease AS d ON d.DiseaseId = dc.DiseaseId
    WHERE cgv.WorkFlowStatusId = 6
      AND ag.EndDate IS NULL
      AND dc.IsNoLongerRecommended = 0
      AND (@HasAgentFilter = 1 AND ag.AgentId = @AgentId OR @HasAgentFilter = 0)
    GROUP BY
      ag.AgentId,
      ag.Name;

    Você ainda precisa do IF, para que possa passar uma constante para o @HasAgentFilterparâmetro, dessa forma você faz o otimizador eliminar o ramo incorreto.

    NÃO passe uma variável para o primeiro parâmetro, apenas uma constante, caso contrário você retornará ao mesmo problema do compilador mencionado na outra resposta (na Solução Naive).

    IF @AgentId = 0
    SELECT a.*
    FROM dbo.GetAgentInfo(0, 0) a;
ELSE
    SELECT a.*
    FROM dbo.GetAgentInfo(1, @AgentId) a;
    • 4

  3. Dependendo da sua versão do SQL, você pode fazer esta sintaxe

    No seu bloco ELSE, atualize:

    and ag.AgentId = @AgentId

    para:

    and(@AgentId = 0 OR ag.AgentId = @AgentId)

    O servidor SQL resolve isso para não aplicar o filtro AND se a primeira expressão for verdadeira, OU ele aplica o filtro AND

    então você pode excluir todo o bloco IF e o ELSE, deixando apenas a consulta do seu bloco ELSE

    • 1

relate perguntas