SQL Server存储过程无法插入新数据，求技术专家协助排查

问题描述 #

我在练习SQL Server时，使用存储过程spInsertPerson执行数据插入操作，但新数据始终无法写入tblPerson表，且确认目标表中不存在该记录。原存储过程代码如下：

USE [dbTest]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spInsertPerson]
    @Id varchar(50),
    @Name varchar(50),
    @PhoneNumber varchar(50),
    @Address varchar(50),
    @StartDate datetime

AS

    Create Table #tmpPersonList
    (
        Id varchar(50),
        name varchar(50),
        phoneNumber varchar(50),
        address varchar(50),
        startdate datetime
    )

    Truncate Table #tmpPersonList
    Insert Into #tmpPersonList
    values(@Id, @Name, @PhoneNumber, @Address, @StartDate)

BEGIN
    
    If (Select Count(*) from #tmpPersonList where Id not in (select PersonID from tblPerson)) > 0
    begin
        print 'Found New Record'
        Insert Into tblPerson(PersonID, Name, PhoneNumber, Address, StartDate) 
        Select Id, name, phoneNumber, address, startDate
        From #tmpPersonList
        where Id not in (select PersonID from tblPerson)
        
    End
    else 
    Begin
        print 'Exists Record'
        Return
    End

End

问题分析 #

1. NOT IN的空值陷阱：如果tblPerson表的PersonID字段存在NULL值，Id NOT IN (SELECT PersonID FROM tblPerson)的逻辑结果会变为未知，导致筛选出的记录数为0，存储过程会误判为记录已存在，跳过插入步骤。
2. 冗余的临时表操作：刚创建的临时表本身就是空的，后续的Truncate Table #tmpPersonList属于完全多余的操作，无意义且增加不必要的开销。

修复方案 #

方案1：简化逻辑，使用NOT EXISTS替代NOT IN #

NOT EXISTS不受NULL值影响，逻辑更稳定，同时可以直接用参数完成判断和插入，移除冗余的临时表：

USE [dbTest]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spInsertPerson]
    @Id varchar(50),
    @Name varchar(50),
    @PhoneNumber varchar(50),
    @Address varchar(50),
    @StartDate datetime
AS
BEGIN
    -- 直接检查目标表是否存在该记录
    IF NOT EXISTS (SELECT 1 FROM tblPerson WHERE PersonID = @Id)
    BEGIN
        PRINT 'Found New Record'
        INSERT INTO tblPerson(PersonID, Name, PhoneNumber, Address, StartDate)
        VALUES(@Id, @Name, @PhoneNumber, @Address, @StartDate)
    END
    ELSE
    BEGIN
        PRINT 'Exists Record'
        RETURN
    END
END

方案2：保留临时表写法，修复NOT IN的空值问题 #

如果一定要保留临时表的使用方式，需要在子查询中排除NULL值：

USE [dbTest]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spInsertPerson]
    @Id varchar(50),
    @Name varchar(50),
    @PhoneNumber varchar(50),
    @Address varchar(50),
    @StartDate datetime
AS
    -- 移除多余的Truncate操作
    Create Table #tmpPersonList
    (
        Id varchar(50),
        name varchar(50),
        phoneNumber varchar(50),
        address varchar(50),
        startdate datetime
    )

    Insert Into #tmpPersonList
    values(@Id, @Name, @PhoneNumber, @Address, @StartDate)

BEGIN
    -- 在NOT IN的子查询中排除NULL值
    If (Select Count(*) from #tmpPersonList where Id not in (select PersonID from tblPerson WHERE PersonID IS NOT NULL)) > 0
    begin
        print 'Found New Record'
        Insert Into tblPerson(PersonID, Name, PhoneNumber, Address, StartDate) 
        Select Id, name, phoneNumber, address, startDate
        From #tmpPersonList
        where Id not in (select PersonID from tblPerson WHERE PersonID IS NOT NULL)
        
    End
    else 
    Begin
        print 'Exists Record'
        Return
    End

End

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