SQL Server存储过程在SSMS与C#执行差异：无效列名异常原因咨询

Why the Discrepancy Between SSMS and C#? #

The core issue boils down to SQL Server's stored procedure compilation rules and execution plan caching behavior, paired with differences in how SSMS and your C# app handle connection/session context. Let's break it down:

1. The Initial Compilation & Plan Cache

When you first create the full-column stagingTable and run the stored procedure in SSMS, SQL Server compiles the procedure and caches an execution plan based on that table's structure. At this point, all referenced columns (like FirstName) exist, so the compilation succeeds without errors.

2. SSMS Reuses the Old Plan (and Gets Lucky)

After deleting and recreating stagingTable with missing columns, running the procedure again in SSMS often reuses the cached execution plan. Here's why it doesn't throw an error:

• The IF EXISTS(...) check runs first, and correctly detects the missing column, so it skips the BEGIN block entirely.
• Since the code referencing the missing column is never executed, the old plan's pre-compiled references to the column never get tested against the new table structure.

3. C# Triggers a Recompilation (And Fails)

In your C# app, one of these scenarios is likely forcing a fresh compilation of the stored procedure, which exposes the column mismatch:

• New connection from the pool: C# uses connection pooling, so the connection you use to call the procedure may be a fresh one that has never executed the procedure before. A fresh compilation is required.
• Metadata change detection: If your C# code recreates stagingTable and calls the procedure in quick succession, SQL Server detects the table structure change and marks the old plan as invalid, forcing a recompile.
• SET option mismatch: SSMS and SqlConnection use different default SET options (like ANSI_NULLS or QUOTED_IDENTIFIER). Stored procedure plans are tied to these options—if they don't match, SQL Server can't reuse the cached plan and must recompile.

During this recompile, SQL Server validates all statically referenced columns in the procedure, even those inside conditional blocks. Since the current stagingTable lacks the column, the compilation fails immediately with the "Invalid Column Name" error.

Fix: Use Dynamic SQL to Avoid Compile-Time Checks #

To get around this, wrap the column-specific logic in dynamic SQL. This delays column validation until execution time, after your IF EXISTS check has confirmed the column exists:

IF EXISTS(SELECT 1 FROM sys.columns WHERE Object_ID = Object_ID(N'stagingTable') AND Name = N'FirstName') 
BEGIN 
    EXEC sp_executesql N'
        UPDATE stagingTable SET errors = ISNULL(errors, '''') + ''First name column is blank.'' WHERE NULLIF(FirstName, '''') IS NULL;
        UPDATE stagingTable SET FirstName = UPPER(FirstName);
    ';
END

Dynamic SQL is parsed and compiled at execution time, so the column existence check has already ensured the referenced column exists before the code runs.

Quick Note #

Static SQL in stored procedures validates all referenced objects/columns at compile time, regardless of conditional logic. This is a SQL Server design choice to catch errors early, but it breaks down when your table structure is dynamic. Dynamic SQL avoids this by shifting validation to runtime.

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