Sql-server – SQL Server 2014 – Strange SP behavior during compilation

sql serversql server 2014stored-procedures

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?

Best Answer

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.

Related Solutions

Postgresql – Table variable not Recognized in stored procedure

There are quite a few problems with this code:

Consecutive calls of select ... into result do no accumulate rows into result, each one overwrites whatever was previously in result.
A variable typed after a table (result of type result_load_status here) is meant to contain only one row of this type, not an entire resultset. For an entire resultset, use CREATE TEMPORARY TABLE or a cursor if possible.
select * from name_of_a_variable does not exist as a valid construct. A temporary table instead of a variable's name would work.

Anyway since accumulating results is already built in RETURN QUERY, you don't need any of the above.

Quoting from the documentation:

RETURN QUERY appends the results of executing a query to the function's result set

So you may just write:

RETURN QUERY execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || b;

RETURN QUERY execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || c;

without any need for the problematic result variable.

Sql-server – Update statement is giving an invalid column name error, even though the column exists

You need to perform the update in dynamic SQL too (or just create the table with all columns in the first place). The error is happening because the update with the new column is being parsed before the dynamic SQL has run.

As an aside, you are probably only getting this error if you try to execute the stored procedure and choose 'Display estimated execution plan' in the toolbar. This is a parsing/binding error that will happen in many scenarios when generating an estimated plan involving #temp tables, but shouldn't happen during normal execution.

So to avoid the error during retrieval of an estimated plan, put any "new" column references in separate dynamic SQL blocks. But to really avoid the problem: stop retrieving estimated execution plans. They're pretty worthless anyway.

Best Answer

Related Solutions

Postgresql – Table variable not Recognized in stored procedure

Sql-server – Update statement is giving an invalid column name error, even though the column exists

Related Question