none
Dead code raise an error RRS feed

  • Question

  • Hello,

    The following code raise an error, but it's dead code. Why we ahe this behavoir ?

    Example with stored procedure :

    DROP TABLE #EntitiesToProceed
    DROP TABLE #Input
    DROP PROCEDURE FOO
    -- 1st time this will raise an error because table does and stored procedure not exists
    GO

    CREATE PROCEDURE FOO
    AS
    BEGIN
    IF 'True'<> 'True'
    BEGIN
       PRINT 'DEAD CODE';
       -- Condition is always false, so we never run this code
       -- Try the same with the following 2 lines commented
    ALTER TABLE #EntitiesToProceed ALTER COLUMN Id NVARCHAR(256) NOT NULL;
    ALTER TABLE #EntitiesToProceed ADD CONSTRAINT PK_EntitiesToProceed_Id PRIMARY KEY (Id);
    END
    END
    GO

    CREATE TABLE #Input
    (
    Id int null
    )

    SELECT * INTO #EntitiesToProceed FROM #Input;

    EXECUTE sp_executesql N'FOO';

    --We have the following error : why ?
    --Msg 8111, Level 16, State 1, Procedure FOO, Line 8
    --Cannot define PRIMARY KEY constraint on nullable column in table '#EntitiesToProceed'.
    --Msg 1750, Level 16, State 0, Procedure FOO, Line 8
    --Could not create constraint. See previous errors.

    Note if the code in stored procedure is done out of the stored procedure, we do not have the error...

    Example without stored procedure 

    DROP TABLE #EntitiesToProceed
    DROP TABLE #Input
    -- 1st time this will raise an error because table does and stored procedure not exists
    GO

    CREATE TABLE #Input
    (
    Id int null
    )

    SELECT * INTO #EntitiesToProceed FROM #Input;

    IF 'True'<> 'True'
    BEGIN
        PRINT 'DEAD CODE';
        -- Condition is always false, so we never run this code
    ALTER TABLE #EntitiesToProceed ALTER COLUMN Id NVARCHAR(256) NOT NULL;
    ALTER TABLE #EntitiesToProceed ADD CONSTRAINT PK_EntitiesToProceed_Id PRIMARY KEY (Id);
    END
    -- Now we do not have any error

    Regards,

    Jean-Pierre

    Thursday, March 10, 2016 2:54 PM

Answers

  • The source of confusion here is deferred name resolution. If there is an operation in a stored procedure, or in a loose batch for that matter, that refers to a non-existing table, this does not result an error on compilation, but only when the statement is reached, if the table is still missing at this point.

    On the other hand, if the table does exist, any columns in the table must exist already when the batch is executed. This is decently straightforward with DML, but more complicated with DDL, because for some DDL statements, no checks are carried out to run-time anyway.

    Generally, you cannot add a column to a table and then refer to it in the same batch. The exception is that if you also create the table in the batch. If you would add a "go" before the IF statement in the example without stored procedure, you would also see the error.

    If we go back to C#, if you refer to a non-existing class member, that is an error, even if the code is dead. (Or so I would assume.)

    Sunday, March 13, 2016 9:57 PM
  • Hi Erland,

    Thanks for your explanation. Indeed, what you said explains the observed behavior.

    To simplify, here a simpler example  :

    ===========================

    DROP

    TABLE #EntitiesToProceed

    DROP

    TABLE #Input

    -- 1st time this will raise an error because table does and stored procedure not exists

    GO

    CREATE

    TABLE #Input( Id int null)

    GO

    SELECT

    * INTO #EntitiesToProceed FROM #Input; 

    --GO  -- If we uncomment this GO, we have the Error

    ALTER

    TABLE #EntitiesToProceed ALTER COLUMN Id NVARCHAR(256) NOT NULL;

    ALTER

    TABLE #EntitiesToProceed ADD CONSTRAINT PK_EntitiesToProceed_Id PRIMARY KEY (Id);

    ===========================

    Like you said Eland, if the commented GO is uncommented, the batch fails with the same error I had previously...

    Thanks for your explanation, I understand better how SQL Server runs.

    Jean-Pierre


    Tuesday, March 15, 2016 10:11 AM

All replies

  • The following code raise an error, but it's dead code. Why we ahe this behavoir ?

    Hello Jean-Piere,

    In T-SQL we don't have "dead code", as we may have in C#. Everything of T-SQL is compiled on execution and that's why you are getting an error here.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, March 10, 2016 2:59 PM
    Moderator
  • In T-SQL we don't have "dead code", as we may have in C#. Everything of T-SQL is compiled on execution and that's why you are getting an error here.

    Well, that is not really true. It is true for this particular case, but the behaviour is not exactly consistent. Move the creation of the temp table inside the procedure, for instance, and the procedure will compile.

    Thursday, March 10, 2016 3:13 PM
  • Hello jp.planas

    I think SQL server is not executing the code, it is trying to parsing the code that casing the issue,

    try below for understanding

     
    
     CREATE TABLE #Input
     (
    Id int null,
    col1 int 
     )
    
    IF'True'<>'True'
    BEGIN
       PRINT 'DEAD CODE';
    ALTER TABLE #Input drop COLUMN col1
    END
    select * from #Input
    -- you can see the column is not dropped
    IF'True'<>'True'
    BEGIN
       PRINT 'DEAD CODE';
    ALTER TABLE #Input ADD CONSTRAINT PK_EntitiesToProceed_Id PRIMARY KEY (Id);
    END
    
    /*Msg 8111, Level 16, State 1, Line 4
    Cannot define PRIMARY KEY constraint on nullable column in table '#Input'.
    Msg 1750, Level 16, State 0, Line 4
    Could not create constraint. See previous errors.
    */

    in my SQL server 2008 R2 I get error on both execution with in SP or outside SP

    please let me know for clarification


    Thanks Saravana Kumar C

    Thursday, March 10, 2016 3:13 PM
  • Hi Olaf,

    First, thanks for your answer.

    Ok I understand that SQL server compile on execution.

    But why do we have a difference between code in a Stored Procedure or without Stored Procedure ?

    Does without Stored Procedure, SQL Server compile statement by statement, and SQL Server compile stored procedure entirely ?

    And why do we have this error "Cannot define PRIMARY KEY constraint on nullable column" on Line 8 (On Line 7 we force the column not nullable...)?

    I find it very mysterious...

    Regards,

    Jean-Pierre

    Sunday, March 13, 2016 9:43 PM
  • Hi Erland,

    This completes my misunderstanding of how the compilation of SQL Server is implemented.

    I wish I had an explanation for these différences.

    Thanks for your answer.

    Jean-Pierre

    Sunday, March 13, 2016 9:48 PM
  • The source of confusion here is deferred name resolution. If there is an operation in a stored procedure, or in a loose batch for that matter, that refers to a non-existing table, this does not result an error on compilation, but only when the statement is reached, if the table is still missing at this point.

    On the other hand, if the table does exist, any columns in the table must exist already when the batch is executed. This is decently straightforward with DML, but more complicated with DDL, because for some DDL statements, no checks are carried out to run-time anyway.

    Generally, you cannot add a column to a table and then refer to it in the same batch. The exception is that if you also create the table in the batch. If you would add a "go" before the IF statement in the example without stored procedure, you would also see the error.

    If we go back to C#, if you refer to a non-existing class member, that is an error, even if the code is dead. (Or so I would assume.)

    Sunday, March 13, 2016 9:57 PM
  • Hi Erland,

    Thanks for your explanation. Indeed, what you said explains the observed behavior.

    To simplify, here a simpler example  :

    ===========================

    DROP

    TABLE #EntitiesToProceed

    DROP

    TABLE #Input

    -- 1st time this will raise an error because table does and stored procedure not exists

    GO

    CREATE

    TABLE #Input( Id int null)

    GO

    SELECT

    * INTO #EntitiesToProceed FROM #Input; 

    --GO  -- If we uncomment this GO, we have the Error

    ALTER

    TABLE #EntitiesToProceed ALTER COLUMN Id NVARCHAR(256) NOT NULL;

    ALTER

    TABLE #EntitiesToProceed ADD CONSTRAINT PK_EntitiesToProceed_Id PRIMARY KEY (Id);

    ===========================

    Like you said Eland, if the commented GO is uncommented, the batch fails with the same error I had previously...

    Thanks for your explanation, I understand better how SQL Server runs.

    Jean-Pierre


    Tuesday, March 15, 2016 10:11 AM
  • Hi jp.planas,

    Glad to hear that the issue is resolved. Thanks for your sharing, you can mark your reply as an answer, other community members could benefit from your solution.

    Thanks,
    Ice Fan


    Ice Fan
    TechNet Community Support


    Wednesday, March 16, 2016 10:11 AM