Stored Procedure - Error not found when temp table is used in join

  • Dear all,

    what I learned is that Stored Procedures (SP) are not compiled before they are executed the first time, but a syntax check takes place when creating it.

    What I don't understand is, that e.g. a wrong table column in a join is not leading to an error message as soon as a temp table is involved.

    Pls check the example below:

    The first procedure is successfully created and the error occours when it is executed.

    When I replace the temp table with a table variable, the SP cannot be created.

    That is a difference between SAP ASE (error in first example) and SQL Server that I stumbled upon while migrating, and this hurts me a lot:)

    1. can so. explain the difference between the two examples?

    2. Is there a way how to can error check the procedure in example 1 before executing it?

    create table a (a int)
    go
    create or alter proc test
    as
    begin
    create table #c (c int)
    select * from #c C join a A on A.ZZZZ = C.c
    end -- successful

    exec test -- Invalid column name 'ZZZZ'
    go

    create or alter proc test
    as
    begin
    declare @c table (c int)
    select * from @c C join a A on A.ZZZZ = C.c
    end -- Invalid column name 'ZZZZ'

  • There is no column ZZZZ in table a. The only column in table a is a

    create table a (a int) would need to change to create table a (ZZZZ int)

  • Ross McMicken wrote:

    There is no column ZZZZ in table a. The only column in table a is a

    create table a (a int) would need to change to create table a (ZZZZ int)

    That's not the point. The poster is asking why SQL Server allows the creation of a proc containing such an obvious error, not how to fix it.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Because SQL, deliberately, has delayed verification.  This allows you to create a proc before other objects exist.  For example, this should run fine:

    USE tempdb;

    GO

    CREATE PROC proc1 AS SELECT * FROM dbo.nosuchtable;

    GO

    DROP PROC proc1

     

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Arthur Kirchner wrote:

    1. can so. explain the difference between the two examples?

    <Deleted incorrect information suggesting columns can be added to existing tables within a stored procedure.>

    Variables can not be conditionally declared so any reference to a variable must be accurate, which is why the code with the table variable doesn't compile. One might expect the code below to fail because the declare is inside a conditional block that won't be executed, however, the table variable is declared and the query completes. The table variable is empty because the insert did follow the conditional logic.

    if 1 = 2
    begin
    declare @this table (that int)
    insert @this values (1)
    end

    select *
    from @this

    • This reply was modified 1 week, 5 days ago by  Ed B. Reason: Removed bad information
    1. At a guess I would say the difference stems from the fact that because the temp table isn't actually created and thus has no metadata the compiler can check against. In contrast the table variable becomes part of the sp and therefore has metadata available for checking. And because one side of the join is not present the compiler drops checking the other side.
    2. I would execute the sp from SSMS with the "Display Estimated Execution Plan" (Ctrl L) option, which will show the error without actually doing anything.

    • This reply was modified 1 week, 6 days ago by  kaj.
  • EDIT - see my corrected conclusion below

     

     

    • This reply was modified 1 week, 5 days ago by  Arthur Kirchner. Reason: sum up of correct answer at the end of the threat
  • Arthur Kirchner wrote:

    It seems that there is no other way than to check the temp table joins in the procedures by hand.

    Not exactly by hand. Intellisense identifies them.

    20240612 Intellisense

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • It's deferred name resolution.

    You can create a procedure if the table does not exist but not if the table exists and the columns do not exist.

  • This was removed by the editor as SPAM

  • Dear all,

    as Phil and Jonathan gave me valuable input I'll correct my conclusion here:

    1. I understand that due to deferred name resolution the temp table is not evaluated  when it is created inside the procedure. Any joins to existing tables are then also "only" syntax- , not metadata-checked. (you get a lot of interesting hits when searching "deferred name resolution")

    2. In SSMS you can identify erroneous joins in the procedure code when Instellisense is enabled. You can set Options|Text editor|Intellisense|Maximum Script size to unlimited, if you want to try sth. funny like paste > 100K of code in one file as I'll try to do.

  • Arthur Kirchner wrote:

    That is a difference between SAP ASE (error in first example) and SQL Server that I stumbled upon while migrating, and this hurts me a lot:)

    I shall address the elephant in the room:

    If the code you are migrating contains errors, presumably it was never executed – so why are you migrating it and how did it get there in the first place?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • ASE code was fine, the procedures had to be migrated and the code changed:

    They were way too complex to accomplish this with the Migration Assistant, and so the way was clear for human errors.

  • Makes sense, thanks for clarifying.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply