Is there a way to force Deferred Name Resolution even if the table exists when creating a stored procedure?
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. ;-)
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