如何移除SQL存储过程中的硬编码国家代码，实现动态逻辑？

现有Country表包含多个国家，应用仅关注其中被标记为interestedCountries的国家，这类国家会随业务逻辑动态变化；另有VendorCheck表存储供应商与国家的组合状态，每个供应商对应每个interestedCountries的状态以列的形式存储（例如ir对应伊朗、cu对应古巴），当某个interestedCountries被移除时，VendorCheck表的对应列也会被删除。

当前应用的存储过程通过硬编码国家代码（如'IR'、'CU'等）来判断需要处理的列，核心逻辑代码如下：

CASE a.countrycode
    WHEN 'IR' THEN d.ir not in ('NOT ANALYZED','REJECTED','LICENCE REQUIRED')
    WHEN 'CU' THEN d.cu not in ('NOT ANALYZED','REJECTED','LICENCE REQUIRED')
    WHEN 'SD' THEN d.sd not in ('NOT ANALYZED','REJECTED','LICENCE REQUIRED')
    WHEN 'SY' THEN d.sy not in ('NOT ANALYZED','REJECTED','LICENCE REQUIRED')
    WHEN 'VE' THEN d.ve not in ('NOT ANALYZED','REJECTED','LICENCE REQUIRED')
    ELSE false
END;

需要将该逻辑改为动态化实现，避免新增或移除interestedCountries时必须修改存储过程，求可行的实现方案。

可行实现方案 #

方案1：重构VendorCheck表结构（推荐） #

当前VendorCheck用列存储每个国家的状态属于反范式设计，扩展性极差。建议重构为行存储的范式结构：

• 新表结构示例：

  CREATE TABLE VendorCheck (
      VendorID INT,
      CountryCode VARCHAR(2), -- 对应Country表的国家代码，如'IR'
      Status VARCHAR(50), -- 存储状态值：'NOT ANALYZED'等
      PRIMARY KEY (VendorID, CountryCode)
  );
  

• 此时原逻辑可以简化为动态关联interestedCountries的查询，完全不需要硬编码：

  EXISTS (
      SELECT 1
      FROM VendorCheck vc
      JOIN Country c ON vc.CountryCode = c.CountryCode
      WHERE vc.VendorID = d.VendorID -- 替换为实际关联字段
        AND c.IsInterested = 1 -- 假设Country表用IsInterested标记关注国家
        AND vc.CountryCode = a.countrycode
        AND vc.Status NOT IN ('NOT ANALYZED','REJECTED','LICENCE REQUIRED')
  )
  

• 优势：后续新增/移除关注国家时，只需修改Country表的标记，无需调整存储过程或表结构，彻底解决扩展性问题。

方案2：使用动态SQL生成逻辑 #

如果暂时无法重构表结构，可以用动态SQL拼接条件，从Country表获取当前的interestedCountries列表，动态生成CASE语句或条件判断：

• 示例存储过程框架（以SQL Server为例）：

  CREATE PROCEDURE GetVendorCheckStatus
  AS
  BEGIN
      DECLARE @CaseLogic NVARCHAR(MAX) = '';
      -- 从Country表获取所有关注国家，拼接CASE分支
      SELECT @CaseLogic = @CaseLogic + 
          'WHEN ''' + CountryCode + ''' THEN d.' + LOWER(CountryCode) + ' NOT IN (''NOT ANALYZED'',''REJECTED'',''LICENCE REQUIRED'') '
      FROM Country
      WHERE IsInterested = 1; -- 标记关注国家的字段
  
      -- 拼接完整SQL语句
      DECLARE @FullSQL NVARCHAR(MAX) = 
          'SELECT 
              -- 其他字段
              CASE a.countrycode
                  ' + @CaseLogic + '
                  ELSE false
              END AS IsValid
          FROM YourTable a
          JOIN VendorCheck d ON -- 关联条件';
  
      -- 执行动态SQL
      EXEC sp_executesql @FullSQL;
  END;
  

• 注意事项：需要处理SQL注入风险（如果CountryCode是用户输入的话，要做转义），同时不同数据库的动态SQL语法略有差异（比如MySQL用PREPARE/EXECUTE）。

方案3：创建辅助函数封装动态判断 #

可以创建一个标量函数，接收CountryCode和供应商ID，内部动态查询VendorCheck表对应列的状态：

• 示例函数（SQL Server）：

  CREATE FUNCTION fn_CheckVendorStatus(@CountryCode VARCHAR(2), @VendorID INT)
  RETURNS BIT
  AS
  BEGIN
      DECLARE @Status VARCHAR(50);
      DECLARE @Sql NVARCHAR(MAX) = 
          'SELECT @Status = ' + LOWER(@CountryCode) + 
          ' FROM VendorCheck WHERE VendorID = @VendorID';
      
      EXEC sp_executesql @Sql, N'@VendorID INT, @Status VARCHAR(50) OUTPUT', 
          @VendorID = @VendorID, @Status = @Status OUTPUT;
  
      RETURN CASE WHEN @Status NOT IN ('NOT ANALYZED','REJECTED','LICENCE REQUIRED') THEN 1 ELSE 0 END;
  END;
  

• 调用时直接用dbo.fn_CheckVendorStatus(a.countrycode, d.VendorID)替代原CASE语句，后续新增关注国家时无需修改函数，只要VendorCheck表新增对应列即可（但还是不如方案1灵活）。

内容的提问来源于stack exchange，提问作者Raida Adn