February 10, 2014 at 9:07 am
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
February 10, 2014 at 9:26 am
yes Begin is missing after as
February 10, 2014 at 10:42 am
(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;
February 10, 2014 at 1:42 pm
I ran into this before... Thanks for the question, Anoo!
February 10, 2014 at 7:29 pm
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
February 10, 2014 at 7:45 pm
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! 🙂
February 11, 2014 at 3:39 pm
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!
February 12, 2014 at 8:48 am
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
February 12, 2014 at 5:31 pm
Good to know. Not yet encountered such scenario. Thanks for sharing!
March 5, 2014 at 4:20 am
Knew it wouldn't work but picked the wrong option why.
March 10, 2014 at 11:49 am
Very interesting question. 🙂
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply