请求排查dbo.CreateFamilyMember存储过程的正确性

你的dbo.CreateFamilyMember存储过程和测试代码存在以下关键问题，导致无法完全满足需求：

1. 插入父母记录后未更新父母的PersonID #

当父母SIN不存在并创建新记录后，未重新获取生成的PersonID，导致后续插入子女记录时，FatherPersonID/MotherPersonID仍为NULL，无法关联到新创建的父母。

2. 未赋值输出参数@PersonID #

存储过程没有将新生成的子女PersonID赋值给@PersonID输出参数，调用方无法获取新增人员的ID。

3. 插入语句未指定列名 #

直接使用VALUES而不指定列名，会在表结构变更（如新增列、调整列顺序）时触发插入错误，同时降低代码可读性。

4. 测试调用参数错误 #

测试代码中向@DateOfDeath和@NetWorth传递空字符串''，会触发DATE和MONEY类型的隐式转换错误，应传递NULL或使用默认值。

修正后的代码 #

优化后的存储过程 #

CREATE OR ALTER PROCEDURE dbo.CreateFamilyMember (
    @SIN            CHAR(9),
    @FirstName      NVARCHAR(50),
    @LastName       NVARCHAR(60),
    @FatherSIN      CHAR(9),
    @MotherSIN      CHAR(9),
    @DateOfBirth    DATE = NULL,
    @DateOfDeath    DATE = NULL,
    @NetWorth       MONEY = 10000,
    @PersonID       INT OUTPUT
)
AS
BEGIN;
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        DECLARE @FatherPersonID INT;
        SELECT @FatherPersonID = dbo.GetPersonID(@FatherSIN);
        IF @FatherPersonID IS NULL
        BEGIN
            -- 指定列名插入父母记录，确保顺序正确
            INSERT INTO dbo.Person (SIN, FatherPersonID, MotherPersonID, FirstName, LastName, DateOfBirth, DateOfDeath, NetWorth)
            VALUES (@FatherSIN, NULL, NULL, NULL, NULL, NULL, NULL, 10000);
            -- 获取新插入的父母PersonID
            SET @FatherPersonID = SCOPE_IDENTITY();
        END

        DECLARE @MotherPersonID INT;
        SELECT @MotherPersonID = dbo.GetPersonID(@MotherSIN);
        IF @MotherPersonID IS NULL
        BEGIN
            INSERT INTO dbo.Person (SIN, FatherPersonID, MotherPersonID, FirstName, LastName, DateOfBirth, DateOfDeath, NetWorth)
            VALUES (@MotherSIN, NULL, NULL, NULL, NULL, NULL, NULL, 10000);
            SET @MotherPersonID = SCOPE_IDENTITY();
        END

        -- 插入子女记录并获取ID
        INSERT INTO dbo.Person (SIN, FatherPersonID, MotherPersonID, FirstName, LastName, DateOfBirth, DateOfDeath, NetWorth)
        VALUES (@SIN, @FatherPersonID, @MotherPersonID, @FirstName, @LastName, @DateOfBirth, @DateOfDeath, @NetWorth);
        SET @PersonID = SCOPE_IDENTITY();

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        -- 抛出错误信息，方便调试
        THROW;
    END CATCH
END;
GO

修正后的测试代码 #

DECLARE @ID INT;
EXEC dbo.CreateFamilyMember 
    @SIN = '643138677', 
    @FirstName = 'Gary', 
    @LastName = 'Martin', 
    @FatherSIN = '501550100', 
    @MotherSIN = '503561001', 
    @DateOfBirth = '2019-12-10',
    @DateOfDeath = NULL,
    @NetWorth = 10000,
    @PersonID = @ID OUTPUT;
SELECT @ID AS NewPersonID;

额外优化建议 #

1. 替代GetPersonID函数：可以直接在存储过程中使用SELECT获取PersonID，避免函数调用开销；或使用MERGE语句一次性完成“查找或插入”操作，提升效率：

   -- 示例：使用MERGE处理父亲记录
   DECLARE @FatherPersonID INT;
   MERGE INTO dbo.Person AS Target
   USING (SELECT @FatherSIN AS SIN) AS Source
   ON Target.SIN = Source.SIN
   WHEN NOT MATCHED THEN
       INSERT (SIN, FatherPersonID, MotherPersonID, FirstName, LastName, DateOfBirth, DateOfDeath, NetWorth)
       VALUES (Source.SIN, NULL, NULL, NULL, NULL, NULL, NULL, 10000)
   OUTPUT inserted.PersonID INTO @FatherPersonID;
   

2. 增加参数验证：添加对必填参数（如@SIN格式、@FirstName/@LastName非空）的验证逻辑，避免无效数据插入。

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