存储过程参数赋值后WITH RECOMPILE放置位置及与OPTION(RECOMPILE)对比

现有写法的问题 #

你当前的写法存在两个不符合需求的问题：

• 写在参数声明后的WITH RECOMPILE是存储过程全局级别的重编译配置，重编译动作会在存储过程开始执行、参数还没被重新赋值时就完成，生成计划用的是初始输入的@AdrID值（或默认值32），不是你后续查询MAX(ID)得到的实际值，计划依旧不匹配。
• 单条INSERT语句后的OPTION(RECOMPILE)只作用于当前这条语句，后续的UPDATE、SELECT语句不会触发重编译，依旧会用错误的参数值生成的旧计划。

两种符合需求的实现方案 #

方案1：单语句追加OPTION(RECOMPILE)（优先推荐） #

给所有用到赋值后@AdrID的语句都加上该选项，每条语句执行时都会用当前最新的参数值重新生成计划，精准控制重编译范围，资源消耗最低。

CREATE PROCEDURE usp_dosomething 
    @AdrID INT = 32
AS
    -- 参数赋值步骤，不需要重编译
    SELECT @AdrID = MAX(ID) FROM Address

    INSERT INTO TempStreet
    SELECT Streetname FROM Workadress WHERE ID = @AdrID
    OPTION(RECOMPILE)

    INSERT INTO TempStreet
    SELECT Streetname FROM Homeadress WHERE ID = @AdrID
    OPTION(RECOMPILE)

    UPDATE TempStreet 
    -- 这里补全你的SET逻辑
    FROM TempStreet INNER JOIN AditionalData1...
    OPTION(RECOMPILE)

    UPDATE TempStreet 
    -- 这里补全你的SET逻辑
    FROM TempStreet INNER JOIN AditionalData2...
    OPTION(RECOMPILE)

    SELECT * FROM TempStreet
    OPTION(RECOMPILE)
GO

注意：建议去掉存储过程名的sp_前缀，该前缀是SQL Server系统存储过程的预留前缀，会导致查询时优先扫描系统库，额外增加性能开销。

方案2：动态SQL封装后续逻辑 #

如果后续业务语句数量较多，逐句加OPTION(RECOMPILE)维护成本高，可以把所有参数赋值后的逻辑封装到动态SQL块中执行，动态SQL每次执行都会自动用传入的最新参数值生成完整的执行计划：

CREATE PROCEDURE usp_dosomething 
    @AdrID INT = 32
AS
    -- 先完成参数赋值
    SELECT @AdrID = MAX(ID) FROM Address

    -- 动态SQL块内的所有语句都会用最新的@AdrID值生成计划
    EXEC sp_executesql N'
    INSERT INTO TempStreet
    SELECT Streetname FROM Workadress WHERE ID = @InnerAdrID

    INSERT INTO TempStreet
    SELECT Streetname FROM Homeadress WHERE ID = @InnerAdrID

    UPDATE TempStreet 
    -- 补全SET逻辑
    FROM TempStreet INNER JOIN AditionalData1...

    UPDATE TempStreet 
    -- 补全SET逻辑
    FROM TempStreet INNER JOIN AditionalData2...

    SELECT * FROM TempStreet
    ', N'@InnerAdrID INT', @InnerAdrID = @AdrID
GO

两个重编译选项的适用场景对比 #

• WITH RECOMPILE（存储过程级别）：作用范围为整个存储过程的所有语句，重编译发生在过程启动时，适合存储过程每次执行的参数差异都极大、完全不需要复用计划的场景，不适合你的需求，因为重编译时参数还未被重新赋值，生成的计划不匹配实际运行值。
• OPTION(RECOMPILE)（语句级别）：仅作用于加了选项的单条语句，重编译发生在语句执行前，适合单条语句参数波动大、或使用了运行时才能确定值的参数的场景，完全适配你的需求，能精准匹配赋值后的参数值生成对应计划。

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