Temporary table in a batch

  • WilburBud

    SSCrazy

    Points: 2854

    SQLRNNR (2/9/2014)


    Nice question.

    Not sure why someone would try to create the same temp table twice in the same procedure. If needing to use the same temp table then clear it and reinsert. Or just use a new temp table with a new name.

    +1

    I ran into this feature when I was building a stored procedure from a couple of code snippets that happened to use the same name for their temporary tables. Seemed quirky at the time until I read this this article http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx

  • obilleneni

    SSC Veteran

    Points: 209

    yes Begin is missing after as

  • Matt Marston

    SSCrazy

    Points: 2413

    (slightly off topic)

    obilleneni (2/10/2014)


    yes Begin is missing after as

    You're the second one to post this. I too noticed the END at the end of the script but no BEGIN at the start of the procedure. But I would argue the opposite. The procedure is not missing the BEGIN. I would remove the END!.

    I realize this is up for debate, but I strongly discourage the use of BEGIN...END around the body of stored procedures. Why? Because the END is misleading. It does not end the stored procedure. It just encloses a series of T-SQL statements. BEGIN...END is used for control flow such as IF...ELSE or WHILE. See BEGIN...END in BOL.

    Don't believe me? Try executing the following script in its entirety (don't selectively execute parts).

    -- Create a test procedure

    CREATE PROCEDURE #TestBeginEnd(@n int)

    AS

    BEGIN

    SELECT @n AS Input;

    END;

    -- Now execute it once

    EXECUTE #TestBeginEnd @n = 1;

    GO

    -- Now execute it a second time

    EXECUTE #TestBeginEnd @n = 2;

    Did you see what happened? The body of the procedure includes everything in the batch (up to the GO statement). It doesn't end with the END, so the first EXECUTE statement is part of the procedure, making it a recursive procedure. How to correct this if that was not the developer or DBA's intent? Add GO before the first EXECUTE statement. Since the GO is important and the BEGIN...END have no effect on the scope of the stored procedure body I recommend against their use in this context.

    If you feel you must, you can drop the procedure afterwards. But since I demonstrated this using a temporary stored procedure you don't to do so (it will be dropped automatically when you close the connection).

    DROP PROCEDURE #TestBeginEnd;

  • Revenant

    SSC-Forever

    Points: 42467

    I ran into this before... Thanks for the question, Anoo!

  • TomThomson

    SSC Guru

    Points: 104773

    Wilbur Bud (2/10/2014)


    I ran into this feature when I was building a stored procedure from a couple of code snippets that happened to use the same name for their temporary tables. Seemed quirky at the time until I read this this article http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx

    Paul's "Temporary tables in stored procedures have a number of characteristics that may seem unexpected", "It is possible that one or more of these behaviours is unintentional", and "they have been this way for a very long time, and so are very unlikely to be changed" are a far more polite way of expressing my comment in an earlier post in this topic. :hehe:

    Tom

  • Ken Wymore

    SSCoach

    Points: 16642

    Revenant (2/10/2014)


    I ran into this before... Thanks for the question, Anoo!

    + 1. I too have run into this and I just changed the code to delete and reload the temp table versus coming up with a second temp table name and then having to track down each reference thereafter. I never bothered looking up the reason for that limitation as I thought it was a poor coding decision to begin with! 🙂

  • Marcia J

    SSCertifiable

    Points: 5649

    L' Eomot Inversé (2/9/2014)


    ...sometimes (and it seems reasonably certain that this is one of those times) "it's by design" means "someone made a mess of this, but there's not much point in fixing it so we'll pretend that's what was intended".

    Love it!

  • Anoo S Pillai

    Mr or Mrs. 500

    Points: 547

    My mistake, shame on me for the silly mistake of the additional keyword 'End' at the end in the question ( as pointed out by some of the members) - Anoo

  • Amit Raut

    SSCrazy

    Points: 2613

    Good to know. Not yet encountered such scenario. Thanks for sharing!

  • marlon.seton

    SSCrazy

    Points: 2623

    Knew it wouldn't work but picked the wrong option why.

  • sqlnaive

    SSCoach

    Points: 17435

    Very interesting question. 🙂

Viewing 11 posts - 16 through 26 (of 26 total)

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