0

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?

5

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.

  • Thank you Scott for fast and valuable answer ! I'm going to solve it via dynamic SQL. – pdata Sep 27 '17 at 9:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.