SQL Server存储过程：如何合并名称存在性与类型校验？

我正在编写[IBT].[save_ibui_attribute]存储过程，需要校验传入的@p_attr_name是否已存在于IBT.ibt_attribute表中。该表的is_group字段用于标识名称对应的是属性还是组，我需要根据类型抛出对应错误。请问是否需要分两次查询，还是可以在EXISTS语句中完成校验？以下是我的两种代码写法：

原代码片段 #

CREATE PROCEDURE [IBT].[save_ibui_attribute]
  @p_attr_id           INT          = NULL,
  @p_is_group          BIT          = 0,
  @p_attr_name         VARCHAR (20),
  @p_attr_desc         VARCHAR (250),
  @p_parent_attr_id    INT,
  @p_attr_level        VARCHAR (20),
  @p_attr_data_type    VARCHAR (8),
  @p_last_updt_user_nm VARCHAR (30) = NULL,
  @p_last_updt_dtime   DATETIME     = NULL,
  @debug AS            BIT          = 0
AS
BEGIN
  BEGIN TRY
    SET NOCOUNT ON;

    DECLARE
      @logfile VARCHAR (MAX),
      @msg     VARCHAR (255);

    SET @msg = 'IBT.save_ibui_attribute Starts';

    IF @debug = 1
    BEGIN
        SELECT 'IBT.save_ibui_attribute DEBUG START';
    END;

    IF @p_attr_id IS NULL
    BEGIN
        IF EXISTS (SELECT attr_name
                   FROM IBT.ibt_attribute
                   WHERE UPPER(attr_name) = UPPER(@p_attr_name))
        BEGIN
            IF ATTRIBUTE
                RAISERROR ( 50001, 16, 1, "ERROR! : New attribute has a name that is already in use. Please use a different attribute name" );
            ELSE 
                IF GROUP
                   RAISERROR (50001, 16, 1, "ERROR! : New group has a name that is already in use. Please use a different group name" );
        END;
    END;
END;

修改后的代码片段 #

declare @isGroup  bit;
    IF @p_attr_id IS NULL
    BEGIN
      
     SET  @isGroup = (SELECT is_group
                      FROM IBT.ibt_attribute
                      WHERE UPPER(attr_name) = UPPER(@p_attr_name))

      IF  @isGroup = 0
        RAISERROR ( 50001, 16, 1, "ERROR! : New attribute has a name that is aleady in use. Please use a different attribute name" );
        ELSE IF @isGroup = 1
         RAISERROR ( 50001, 16, 1, "ERROR! : New group has a name that is aleady in use. Please use a different group name" );
      END;
    END;

不需要分两次查询，一次查询就能完成校验和类型判断，同时还能避免语法错误和空值问题：

问题分析 #

1. 原代码存在语法错误：IF ATTRIBUTE和IF GROUP没有对应的变量或字段引用，无法正确判断类型。
2. 修改后的代码存在逻辑漏洞：如果查询不到匹配的记录，@isGroup会被赋值为NULL，此时@isGroup = 0和@isGroup = 1的判断都会不成立，导致无法抛出错误。

优化后的代码 #

通过一次查询获取is_group的值，同时处理记录不存在的情况：

CREATE PROCEDURE [IBT].[save_ibui_attribute]
  @p_attr_id           INT          = NULL,
  @p_is_group          BIT          = 0,
  @p_attr_name         VARCHAR (20),
  @p_attr_desc         VARCHAR (250),
  @p_parent_attr_id    INT,
  @p_attr_level        VARCHAR (20),
  @p_attr_data_type    VARCHAR (8),
  @p_last_updt_user_nm VARCHAR (30) = NULL,
  @p_last_updt_dtime   DATETIME     = NULL,
  @debug AS            BIT          = 0
AS
BEGIN
  BEGIN TRY
    SET NOCOUNT ON;

    DECLARE
      @logfile VARCHAR (MAX),
      @msg     VARCHAR (255),
      @exists_is_group BIT;

    SET @msg = 'IBT.save_ibui_attribute Starts';

    IF @debug = 1
    BEGIN
        SELECT 'IBT.save_ibui_attribute DEBUG START';
    END;

    IF @p_attr_id IS NULL
    BEGIN
        -- 一次查询获取匹配记录的is_group值，无匹配则为NULL
        SELECT @exists_is_group = is_group
        FROM IBT.ibt_attribute
        WHERE UPPER(attr_name) = UPPER(@p_attr_name);

        -- 判断是否存在匹配记录
        IF @exists_is_group IS NOT NULL
        BEGIN
            IF @exists_is_group = 0
                RAISERROR (50001, 16, 1, 'ERROR! : New attribute has a name that is already in use. Please use a different attribute name');
            ELSE
                RAISERROR (50001, 16, 1, 'ERROR! : New group has a name that is already in use. Please use a different group name');
        END;
    END;
END;
END TRY
BEGIN CATCH
    -- 可添加异常处理逻辑
    THROW;
END CATCH
END;

另一种写法：直接在EXISTS中结合条件判断 #

如果不需要单独存储is_group的值，也可以通过两次EXISTS判断（本质还是一次表扫描逻辑，推荐上面的写法更高效）：

IF @p_attr_id IS NULL
BEGIN
    IF EXISTS (SELECT 1 FROM IBT.ibt_attribute WHERE UPPER(attr_name) = UPPER(@p_attr_name) AND is_group = 0)
    BEGIN
        RAISERROR (50001, 16, 1, 'ERROR! : New attribute has a name that is already in use. Please use a different attribute name');
    END
    ELSE IF EXISTS (SELECT 1 FROM IBT.ibt_attribute WHERE UPPER(attr_name) = UPPER(@p_attr_name) AND is_group = 1)
    BEGIN
        RAISERROR (50001, 16, 1, 'ERROR! : New group has a name that is already in use. Please use a different group name');
    END;
END;

关键注意事项 #

• 性能优化：如果attr_name字段数据量大，建议创建计算列索引UPPER(attr_name)，避免全表扫描。
• 并发处理：多用户操作场景下，建议添加事务或使用WITH (UPDLOCK, HOLDLOCK)提示，防止插入重复数据。

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