T-SQL存储过程IF/ELSE更新@customer_id后插入item表报NULL错误

你的存储过程报错不是变量作用域问题，是ELSE分支的查询条件逻辑错误：
你在判断客户存在后，尝试查询customer_id赋值给@customer_id时，写的查询条件是WHERE customer_id = @customer_id。此时@customer_id是刚声明的未赋值变量，默认值为NULL，SQL中NULL和任何值做相等判断结果都是UNKNOWN，所以这条查询永远返回空结果，@customer_id不会被赋值，最终还是NULL，插入item表时就触发了非空约束报错。

另外你原代码里判断客户是否存在的条件使用的字段是address，但插入客户时用的字段是customer_address，如果是表字段名笔误也会导致逻辑异常，修复时需要统一字段名。

把ELSE分支的查询条件替换为和判断客户存在时一致的地址匹配条件即可，修正后完整代码如下：

CREATE PROCEDURE dbo.uspInsertRecord
    @serial_num AS INT,
    @customer_address AS VARCHAR(30)
AS 
    DECLARE @customer_id AS UNIQUEIDENTIFIER;

    IF NOT EXISTS (SELECT 1 FROM dbo.customer WHERE customer_address = @customer_address)
    BEGIN 
        SET @customer_id = NEWID()
        INSERT INTO dbo.customer (customer_id, customer_address)
        VALUES (@customer_id, @customer_address)
    END
    ELSE
    BEGIN
        -- 修正：用地址匹配查询现有客户的customer_id
        SELECT @customer_id = customer_id 
        FROM dbo.customer
        WHERE customer_address = @customer_address
    END

    IF NOT EXISTS (SELECT 1 FROM dbo.item WHERE serial_num = @serial_num)
    BEGIN
        INSERT INTO dbo.item (serial_num, customer_id)
        VALUES (@serial_num, @customer_id)
    END

• 如果有并发插入同地址客户的场景，建议给customer.customer_address字段加唯一约束，同时给存储过程加事务和锁提示，避免重复插入客户。
• 可以用MERGE语句简化客户的Upsert（存在则取ID、不存在则插入）逻辑，减少重复的条件判断。

内容的提问来源于stack exchange，提问作者jaredlee.dev