SQL EXISTS语句异常：实验室存储过程记录检查失效排查

问题描述 #

我有一个用于插入实验室记录的存储过程，此前数月运行正常，但近月来记录检查功能失效。已知SampleName存在且可通过查询定位，将原IF EXISTS语句替换为以下语句后可部分正常工作：

IF EXISTS (SELECT [SampleName] FROM [dbo].[HPLC_Sample] 
           WHERE [SampleName] LIKE '%' + UPPER(@SampleName) + '%')

存储过程完整代码如下：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_HPLC_Params]
    @SampleName nvarchar(32),
    @DataFile nvarchar(max),
    @AcqInstrument nvarchar(50),
    @AnalysisDate datetime,
    @ResultsCreated datetime,
    @ResCreatedBy nvarchar(50),
    @AcqMethod nvarchar(50),
    @InjDate datetime,
    @AcqOp datetime,
    @SeqLine int,
    @Location int,
    @Inj int,
    @InjVol int,
    @ActualInj int,
    @SeqFile nvarchar(50),
    @StartPress float,
    @StopPress float, 
    @StartFlow float,
    @StopFlow float,
    @SortBy nvarchar(50),
    @CalbDTCreate datetime,
    @CalbDTMod datetime,
    @PeakID nchar(10),
    @Muliplier int,
    @Dilution int,
    @UnCalPeaks nchar(10),
    @NumSignals int,
    @NumErrors int
AS
BEGIN
    DECLARE @SampleID AS smallint
    
    IF EXISTS (SELECT [SampleName] FROM [dbo].[HPLC_Sample] 
               WHERE [SampleName] = @SampleName)
    BEGIN
        SELECT @SampleID = [SampleID] 
        FROM [dbo].[HPLC_Sample] 
        WHERE [SampleName] = @SampleName
    END
    ELSE
    BEGIN
        INSERT INTO HPLC_Sample([SampleName])   
        VALUES (UPPER(@SampleName))

        SELECT @SampleID = [SampleID] 
        FROM [dbo].[HPLC_Sample] 
        WHERE [SampleName] = @SampleName
    END
END
BEGIN
    SET NOCOUNT ON;

    BEGIN
        INSERT INTO HPLC_Params([SampleID], [Data File], [Acq Instrument], [Analysis Date], [Results Created],
                [Results Created By], [Acq Method], [Injection Date], [Acq Operator],
                [Seq Line], [Location], [Inj], [Inj Volume], 
                [Actual Inj Volume], [Sequence File], [Start Pressure], [Stop Pressure],
                [Start Flow], [Stop Flow], [Sorted By], [Calib Data Created],
                [Calib Data Modified], [Peak ID], [Multiplier],[Dilution],
                [Uncalibrated Peaks], [Number of Signals], [Number of Errors])  
        VALUES (@SampleID, @DataFile, @AcqInstrument, @AnalysisDate, @ResultsCreated,
                @ResCreatedBy, @AcqMethod, @InjDate, @AcqOp,
                @SeqLine, @Location, @Inj, @InjVol,
                @ActualInj, @SeqFile, @StartPress, @StopPress, 
                @StartFlow, @StopFlow, @SortBy, @CalbDTCreate,
                @CalbDTMod, @PeakID, @Muliplier, @Dilution,
                @UnCalPeaks, @NumSignals, @NumErrors)
    END 
END

可能的失效原因 #

1. 大小写处理不一致 #

原代码中，记录检查用的是精确匹配SampleName = @SampleName，但插入新记录时却将@SampleName转为大写存入数据库。这会导致：

• 当传入的@SampleName为小写或混合大小写时，无法匹配到数据库中已存在的大写样本名，误判为记录不存在，进而重复插入相同样本的大写版本。
• 替换为UPPER(@SampleName)结合LIKE的语句后，能匹配到数据库中的大写记录，因此部分恢复正常。

2. 隐藏空白字符干扰 #

传入的@SampleName或数据库中存储的SampleName可能包含前导、尾随或中间的空白字符（比如输入时误加空格）：

• 精确匹配=会因为空白字符的存在导致匹配失败，而LIKE搭配通配符%会忽略首尾空白（或允许额外字符存在），所以能匹配到目标记录。
• 可以通过以下语句验证：

SELECT * FROM HPLC_Sample 
WHERE LTRIM(RTRIM(SampleName)) = LTRIM(RTRIM(@SampleName))

如果能返回结果，说明存在空白字符问题。

3. 数据库排序规则区分大小写 #

如果HPLC_Sample表的SampleName列使用区分大小写的排序规则（如SQL_Latin1_General_CP1_CS_AS，其中CS代表Case-Sensitive），那么精确匹配时会严格区分大小写，导致原判断失效。而替换后的语句通过UPPER()统一转为大写，规避了排序规则的大小写限制。

• 可以通过以下语句查看列的排序规则：

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'HPLC_Sample' AND COLUMN_NAME = 'SampleName'

4. 逻辑冗余导致的二次匹配失败 #

原代码插入新记录后，再次用SampleName = @SampleName查询SampleID，这同样会因为上述大小写或空白问题，无法查询到刚插入的记录，最终@SampleID可能为NULL，导致后续插入HPLC_Params时出现数据异常。

优化建议 #

针对上述问题，建议统一大小写与空白处理，并简化逻辑避免重复查询：

ALTER PROCEDURE [dbo].[sp_HPLC_Params]
    @SampleName nvarchar(32),
    @DataFile nvarchar(max),
    @AcqInstrument nvarchar(50),
    @AnalysisDate datetime,
    @ResultsCreated datetime,
    @ResCreatedBy nvarchar(50),
    @AcqMethod nvarchar(50),
    @InjDate datetime,
    @AcqOp datetime,
    @SeqLine int,
    @Location int,
    @Inj int,
    @InjVol int,
    @ActualInj int,
    @SeqFile nvarchar(50),
    @StartPress float,
    @StopPress float, 
    @StartFlow float,
    @StopFlow float,
    @SortBy nvarchar(50),
    @CalbDTCreate datetime,
    @CalbDTMod datetime,
    @PeakID nchar(10),
    @Muliplier int,
    @Dilution int,
    @UnCalPeaks nchar(10),
    @NumSignals int,
    @NumErrors int
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SampleID AS smallint
    DECLARE @CleanSampleName nvarchar(32) = LTRIM(RTRIM(UPPER(@SampleName)))
    
    -- 统一处理后匹配样本ID
    SELECT @SampleID = [SampleID] 
    FROM [dbo].[HPLC_Sample] 
    WHERE LTRIM(RTRIM(UPPER([SampleName]))) = @CleanSampleName

    IF @SampleID IS NULL
    BEGIN
        -- 插入处理后的样本名
        INSERT INTO HPLC_Sample([SampleName])   
        VALUES (@CleanSampleName)
        -- 直接获取刚插入的ID，避免二次查询
        SELECT @SampleID = SCOPE_IDENTITY()
    END

    -- 插入参数记录
    INSERT INTO HPLC_Params([SampleID], [Data File], [Acq Instrument], [Analysis Date], [Results Created],
            [Results Created By], [Acq Method], [Injection Date], [Acq Operator],
            [Seq Line], [Location], [Inj], [Inj Volume], 
            [Actual Inj Volume], [Sequence File], [Start Pressure], [Stop Pressure],
            [Start Flow], [Stop Flow], [Sorted By], [Calib Data Created],
            [Calib Data Modified], [Peak ID], [Multiplier],[Dilution],
            [Uncalibrated Peaks], [Number of Signals], [Number of Errors])  
    VALUES (@SampleID, @DataFile, @AcqInstrument, @AnalysisDate, @ResultsCreated,
            @ResCreatedBy, @AcqMethod, @InjDate, @AcqOp,
            @SeqLine, @Location, @Inj, @InjVol,
            @ActualInj, @SeqFile, @StartPress, @StopPress, 
            @StartFlow, @StopFlow, @SortBy, @CalbDTCreate,
            @CalbDTMod, @PeakID, @Muliplier, @Dilution,
            @UnCalPeaks, @NumSignals, @NumErrors)
END

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