存储过程替换CASE为IF后过滤失效问题及解决过程

之前用MAX(idcolumn)+1生成ID时遇到并发问题——半秒内两个请求生成了相同ID，于是创建了序列生成器（Sequencer），可以正常工作。但因为无法在CASE表达式中使用NEXT VALUE FOR，只能大幅重构语句。

原代码片段 #

INSERT INTO
[other columns]
VALUES
[F_ID]      
    CASE
        WHEN @P_StartDate = NULL THEN NULL
        ELSE COALESCE(@F_Id , 
            (SELECT CAST(MAX(CAST(F_Id AS Int)) AS nvarchar(7)) FROM dbo.TABLE1 WHERE M_Id = d1.M_Id AND ISNUMERIC(F_Id) = 1 and M_Flag = @M_Flag), 
            (SELECT CAST(MAX(CAST(F_Id AS Int)) AS nvarchar(7)) FROM dbo.TABLE1 WHERE M_Id = d1.M_Id AND ISNUMERIC(F_Id) = 1), 
            (SELECT CAST(COALESCE(MAX(CAST(F_Id AS Int)) + 1,1) AS nvarchar(7)) FROM dbo.TABLE1 WHERE ISNUMERIC(F_Id) = 1))
        END ,
FROM dbo.TABLE1 as d1
WHERE d1.D_UID = @OriginalD_UID     

重构后的问题代码 #

DECLARE @NEWF_ID nvarchar(7)

    IF @P_StartDate IS NULL
        SET @NewF_ID = NULL
    ELSE IF @P_StartDate IS NOT NULL
        BEGIN   
        IF @F_Id IS NOT NULL
            SET @NewF_Id = @F_Id            
        ELSE IF (SELECT MAX(F_Id) FROM dbo.TABLE1 WHERE M_Id = dbo.TABLE1.M_Id AND M_Flag = @M_Flag) IS NOT NULL        
            SET @NewF_ID = (SELECT MAX(F_Id) FROM dbo.TABLE1 WHERE M_Id = dbo.TABLE1.M_Id AND M_Flag = @M_Flag)     
        ELSE IF (SELECT MAX(F_Id) FROM dbo.TABLE1 WHERE M_Id = dbo.TABLE1.M_Id) IS NOT NULL
            SET @NewF_ID = (SELECT MAX(F_Id) FROM dbo.TABLE1 WHERE M_Id = dbo.TABLE1.M_Id)          
        ELSE 
            SET @NewF_ID = CAST(NEXT VALUE FOR F_Id_Sequence as nvarchar(7))        
        END

INSERT INTO
[columns]
VALUES
[@NewF_Id]
FROM dbo.TABLE1 as d1
WHERE d1.D_UID = @OriginalD_UID 

原INSERT语句中的CASE表达式替换为@NewF_ID后，无法在新代码中使用d1.TABLE1关联过滤，且过滤功能失效，逻辑看似未变，寻求指导。

解决后的代码 #

问题根源是M_Id在存储过程中声明过晚，通过提前获取对应M_Id解决：

DECLARE @NEWF_ID nvarchar(7)
DECLARE @M_ID nvarchar(7)

SELECT @M_ID = M_ID FROM dbo.table1 WHERE D_UID = @Original_D_UID

    IF @P_StartDate IS NULL
        SET @NewF_ID = NULL
    ELSE IF @P_StartDate IS NOT NULL
        BEGIN   
        IF @F_Id IS NOT NULL
            SET @NewF_Id = @F_Id            
        ELSE IF (SELECT MAX(F_Id) FROM dbo.TABLE1 WHERE @M_Id = dbo.TABLE1.M_Id AND M_Flag = @M_Flag) IS NOT NULL       
            SET @NewF_ID = (SELECT MAX(F_Id) FROM dbo.TABLE1 WHERE @M_Id = dbo.TABLE1.M_Id AND M_Flag = @M_Flag)        
        ELSE IF (SELECT MAX(F_Id) FROM dbo.TABLE1 WHERE @M_Id = dbo.TABLE1.M_Id) IS NOT NULL
            SET @NewF_ID = (SELECT MAX(F_Id) FROM dbo.TABLE1 WHERE @M_Id = dbo.TABLE1.M_Id)         
        ELSE 
            SET @NewF_ID = CAST(NEXT VALUE FOR F_Id_Sequence as nvarchar(7))        
        END

INSERT INTO
[columns]
VALUES
[@NewF_Id]
FROM dbo.TABLE1 as d1
WHERE d1.D_UID = @OriginalD_UID 

最终解决方法很简单，这也是一次很好的存储过程学习经历。

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