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.