主页 / dba / 问题 / 345003
Accepted
Jefferson
Asked: 2025-01-30 21:22:07 +0800 CST

使用可选 AgentId 获取最大开始日期的存储过程

  • 772

我有此存储过程，我希望根据 AgentId 或不使用 AgentId 获得最大 startDate。我执行此操作的方法是使用 if else，想看看是否有更好的方法来实现此目的？

  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 个回答

  1. Best Answer

    当前解决方案

    您的版本肯定不是最糟糕的。主要问题是您有两次相同的代码，这会带来一些维护开销（即所有更改都必须应用于两个代码块）。

    但是 T-SQL 不是 Java 或 C# - 它不会为您提供许多代码重复数据删除工具（并且它提供的许多重复数据删除工具会使优化器的工作变得更加困难）。应用程序员可能会说“不要重复自己”，但作为 T-SQL 程序员，有时您必须这样做。

    有人可能会合理地主张就此维持现状吧。

    简单的解决方案

    您可以使用具有略微不同的过滤器的单个块： ... and (ag.AgentId = @AgentId OR @AgentId = 0)。但问题是，这OR会使优化器的工作变得更加困难。SQL Server 为查询创建一次执行计划，然后在后续运行中重复使用它（直到计划被推出内存）。这很好，因为它节省了编译时间。然而，这也意味着它总是必须检查是否ag.AgentId = @AgentId，因为它事先不知道您将传递什么值到参数中

    适合小数据集，否则应避免。

    动态解决方案

    这将使优化器的工作更加简单，并且您不会有两次相同的代码。它依赖于动态 SQL，只拥有 SQL 代码的单一副本，并对其进行修改以包含或跳过检查@AgentId。

    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. 如果您确实想避免重复（DRY），并且不想使用动态 SQL，那么您可以使用内联表值函数。

    第二个分支中的TOP (1)似乎是多余的。

    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;

    您仍然需要IF，以便您可以为@HasAgentFilter参数传递一个常量，这样您就可以让优化器省略错误的分支。

    不要为第一个参数传递变量，只传递常量，否则您将回到另一个答案中提到的相同编译器问题（在 Naive Solution 中）。

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

  3. 根据您的 SQL 版本，您可以执行此语法

    在您的 ELSE 块中，更新：

    and ag.AgentId = @AgentId

    到：

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

    如果第一个表达式为真，则 SQL Server 解析此问题，不应用 AND 过滤器，否则应用 AND 过滤器

    然后你可以删除整个 IF 块和 ELSE，只留下来自 ELSE 块的查询

    • 1

相关问题