Is there a way to force Deferred Name Resolution even if the table exists when creating a stored procedure?

Question

When creating a stored procedure in SQL Server, you are allowed to refer to tables which do not exist. But, if the table does exist then any column you refer to in the procedure must exist in that table (Deferred Name Resolution).

Is it possible to instruct SQL Server to defer the name resolution of all tables referenced in a procedure irrespective of whether they exist or not? I do want to keep the general syntax checking, so even if it were possible, hacking the stored procedure definition into a system table isn't an option.

I expect my asking to do this might seem a little bit weird, so here's some background: I auto generate table definitions and stored procedures from an application written in C# and it's very difficult for me to change the code to order the changes as SQL needs them. My code "guarantees" that the schema is consistent within a transaction, but currently I can't guarantee that the table columns are defined before I define the stored procedure which references them.

Below is a canonical example of the SQL created by the C# which "illustrates" the problem I'm trying to solve.

--Say this table already exists.
CREATE TABLE myTable
(
    a NVARCHAR(MAX)
)
GO

--My C# code creates something like this
BEGIN TRAN 
GO

--the stored procedure gets generated first.
CREATE PROCEDURE mySproc
AS
BEGIN
    SELECT a,b FROM myTable
END

--then the table update
ALTER TABLE myTable
    ADD b nvarchar(MAX)

COMMIT TRAN 

It is possible for me to fix this in the C# code, but I'm hoping for a simple "magic" tweak I can pull in the SQL. This will save a lot of time for me.

Answer 1

No.

I feel really guilty just typing that, but no, sadly. That's the first time I've heard of this use case, and it makes perfect sense. Best to submit a request for it on https://feedback.azure.com/forums/908035-sql-server and your grandchildren will be able to do it. ;-)

Answer 2

Just in case you are still interested, there is a potential workaround you can employ. Here is the updated code, which introduces the #deferResolution temporary table to each query in the procedure. Because the temporary table will only exist at runtime, the procedure is able to compile even though the proper columns don't yet exist on myTable.

You will even get the same execution plan (no reference to the #deferResolution table) for each statement in the procedure due to the way that the query optimizer can prove this WHERE NOT EXISTS always evaluates to true.

All that said, this is a terrible hack presented mostly for intellectual interest and there could be an edge case where it breaks down. As Aaron mentions, you would likely be better off making all of your schema changes in the proper order.

--Say this table already exists.
CREATE TABLE myTable
(
    a NVARCHAR(MAX)
)
GO

--My C# code creates something like this
BEGIN TRAN 
GO

--the sproc gets generated first.
CREATE PROCEDURE mySproc
AS
BEGIN
    CREATE TABLE #deferResolution (dummy INT NOT NULL)
    SELECT a,b FROM myTable WHERE NOT EXISTS (SELECT * FROM #deferResolution WHERE 0=1)
END

--then the table update
ALTER TABLE myTable
    ADD b nvarchar(MAX)

COMMIT TRAN