SQL Server 2014 - Strange SP behavior during compilation Today I noticed a strange behavior during the creation of a stored procedure in SQL Server 2014. 
Below is my test procedure. Please explain why SQL Server disallows compilation with the first SELECT and allows compilation with second one.

CREATE PROCEDURE [dbo].[test]
AS
BEGIN

    SET NOCOUNT ON;

    IF 1=0  -- this will never occur
    BEGIN
        select x from Table -- TABLE EXIST BUT COLUMN NOT - STORED PROCEDURE IS NOT CREATED
        --Msg 207, Level 16, State 1, Procedure test, Line 25
        --Invalid column name 'x'.

        -- But if i change 
        select x from TableWhichNotExistInDatabase  -- This table not exist in database, but stored procedure has been created without any problems and I can exec it without errors (    because of 1=0 in if )

    END
    ELSE 
    SELECT ''
END


In my real procedure, I'm creating temporary tables with various schema (from XML passed as parameter). Next I'm trying to validate some data in the stored procedure. Validation blocks are controlled by IF statements. 

The creation of temporary tables and the stored procedure call is executed by a dynamiclly created procedure. 

I think the problem lies somewhere during the creation of the execution plan or something like that. If the table does not exist in the database, SQL Server allows the creation of the stored procedure when a table does not exist.  However, when the table exists, the creation of the stored procedure fails because of invalid schema - no column.

I was looking for a solution which disables schema checking but did not have any success.

Are there any ideas regarding how can I bypass this limitation?
The phenomenon you are experiencing is called Deferred Name Resolution.


  When a stored procedure is created, the statements in the procedure
  are parsed for syntactical accuracy. If a syntactical error is
  encountered in the procedure definition, an error is returned and the
  stored procedure is not created. If the statements are syntactically
  correct, the text of the stored procedure is stored in the
  sys.sql_modules catalog view.
  
  When a stored procedure is executed for the first time, the query
  processor reads the text of the stored procedure from the
  sys.sql_modules catalog view and checks that the names of the objects
  used by the procedure are present. This process is called deferred
  name resolution because table objects referenced by the stored
  procedure need not exist when the stored procedure is created, but
  only when it is executed.
  
  > Deferred name resolution can only be used when you reference
  nonexistent table objects. All other objects must exist at the time
  the stored procedure is created. For example, when you reference an
  existing table in a stored procedure you cannot list nonexistent
  columns for that table.


The only way I know around this problem would be to use dynamic SQL.