Why does Microsoft SQL Server check columns but not tables in stored procs?

Question

Microsoft SQL Server seems to check column name validity, but not table name validity when defining stored procedures. If it detects that a referenced table name exists currently, it validates the column names in a statement against the columns in that table. So, for example, this will run OK:

CREATE PROCEDURE [dbo].[MyProcedure] AS BEGIN     SELECT         Col1, Col2, Col3     FROM         NonExistentTable END GO 

... as will this:

CREATE PROCEDURE [dbo].[MyProcedure] AS BEGIN     SELECT         ExistentCol1, ExistentCol2, ExistentCol3     FROM         ExistentTable END GO 

... but this fails, with 'Invalid column name':

CREATE PROCEDURE [dbo].[MyProcedure] AS BEGIN     SELECT         NonExistentCol1, NonExistentCol2, NonExistentCol3     FROM         ExistentTable END GO 

Why does SQL Server check columns, but not tables, for existence? Surely it's inconsistent; it should do both, or neither. It's useful for us to be able to define SPs which may refer to tables AND/OR columns which don't exist in the schema yet, so is there a way to turn off SQL Server's checking of column existence in tables which currently exist?

Answer 1

This is called deferred name resolution.

There is no way of turning it off. You can use dynamic SQL or (a nasty hack!) add a reference to a non existent table so that compilation of that statement is deferred.

CREATE PROCEDURE [dbo].[MyProcedure] AS BEGIN  CREATE TABLE #Dummy (c int)      SELECT         NonExistantCol1, NonExistantCol2, NonExistantCol3     FROM         ExistantTable      WHERE NOT EXISTS(SELECT * FROM #Dummy)       DROP TABLE #Dummy  END GO