SQL Server调用含CROSS APPLY的嵌套存储过程出现列无效错误

This is a common pain point tied to how SQL Server handles temp table metadata during stored procedure compilation. Let’s break down why this happens and walk through actionable fixes:

Likely Root Cause #

The most probable issue is that your nested stored procedure uses SELECT ... INTO to dynamically create the temp table referenced in the CROSS APPLY clause. When SQL Server compiles a stored procedure, it checks for object existence—but for temp tables created via SELECT INTO, the column structure (like your levels column) isn’t known at compilation time. Deferred name resolution only covers the table itself, not its individual columns.

• When you run the nested proc directly, the first execution compiles the query with the actual temp table column metadata available, so the valid plan gets cached.
• But if you call it from the main proc first (without ever running the nested proc standalone), compilation happens before the temp table’s columns are created, leading to the "invalid column name" error.

Fixes to Try #

1. Explicitly Define the Temp Table Structure #

Replace SELECT ... INTO with a explicit CREATE TABLE statement to define the temp table’s columns upfront. This lets SQL Server know exactly what columns exist at compilation time:

-- Inside your nested stored procedure
CREATE TABLE #TempResults (
    levels INT,
    -- Add all other columns with their correct data types
);

INSERT INTO #TempResults (levels, [OtherColumns])
SELECT levels, [OtherColumns]
FROM YourSourceData;

-- Now use the temp table in your CROSS APPLY
SELECT main.*, ca.*
FROM MainTable main
CROSS APPLY (
    SELECT * FROM #TempResults WHERE [SomeCondition] = main.Id
) AS ca;

2. Force Query Recompilation #

If you want to keep using SELECT ... INTO, add the OPTION (RECOMPILE) hint to the query containing the CROSS APPLY. This ensures SQL Server recompiles the query each time the proc runs, so it can see the temp table’s current column structure:

SELECT main.*, ca.*
FROM MainTable main
CROSS APPLY (
    SELECT * FROM #TempResults WHERE [SomeCondition] = main.Id
) AS ca
OPTION (RECOMPILE);

3. Check for Temp Table Name Collisions #

Double-check that your main stored procedure isn’t creating a temp table with the same name as the one in the nested proc. While this usually throws a "duplicate object" error, edge cases with nested scope can lead to unexpected column mismatches.

4. Use Table Variables (With Caveats) #

Table variables have their structure defined at declaration time, so SQL Server knows their columns during compilation. Just note that table variables have limited indexing support and can perform worse than temp tables for large datasets:

-- Inside your nested stored procedure
DECLARE @TempResults TABLE (
    levels INT,
    -- Other columns
);

INSERT INTO @TempResults (levels, [OtherColumns])
SELECT levels, [OtherColumns]
FROM YourSourceData;

-- Use the table variable in CROSS APPLY
SELECT main.*, ca.*
FROM MainTable main
CROSS APPLY (
    SELECT * FROM @TempResults WHERE [SomeCondition] = main.Id
) AS ca;

Why Running the Nested Proc First Works #

When you execute the nested proc directly before calling the main proc, SQL Server caches an execution plan that includes the correct temp table column metadata. The main proc then uses this cached plan, skipping the problematic compilation step where the column wasn’t recognized. This confirms the issue is tied to compilation context, not the underlying logic.

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