December 18, 2017 at 9:43 pm
Comments posted to this topic are about the item Temporary tables and procedures
God is real, unless declared integer.
December 18, 2017 at 10:43 pm
Good question, many thanks.
...
December 19, 2017 at 7:18 am
Good question.
My Question is why? Doesn't the Procedure run within a separate session, and aren't #temp table session specific ??
December 19, 2017 at 7:24 am
Yes, a procedure RUNs and COMPILES with a separat scope / copy of the #temp table. But (as written) there seems to be a bug in the parser / syntax checker, which is executed before compile / run.
God is real, unless declared integer.
December 19, 2017 at 7:25 am
Interesting, and definitely not what I expected.
Very curious.
December 19, 2017 at 7:50 pm
Weird
December 19, 2017 at 11:59 pm
I have encountered this on a few occasions.
you are correct that it is very difficult to debug / trace, especially where you have triggers that execute stored procs...
Thanks for the question, Thomas
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
December 20, 2017 at 2:40 am
Budd - Tuesday, December 19, 2017 7:18 AMGood question.
My Question is why? Doesn't the Procedure run within a separate session, and aren't #temp table session specific ??
No, a procedure runs in the same session as the calling session, a quick, and very simple test is to create a temporary procedure, that simply selects session ID, then call it and compare the session ID to the parent session:
CREATE PROCEDURE #Proc
AS
SELECT @@SPID;
GO
SELECT @@SPID;
EXECUTE #Proc;
You will find the ID returned is the same.
t.franz - Tuesday, December 19, 2017 7:24 AMYes, a procedure RUNs and COMPILES with a separat scope / copy of the #temp table. But (as written) there seems to be a bug in the parser / syntax checker, which is executed before compile / run.
As above, the procedure runs in the same session as the parent, therefore you would expect the procedure to be able to access any temporary tables created by the parent. This is also covered in BOL under CREATE TABLE:
A local temporary table created within a stored procedure or trigger can have the same name as a temporary table that was created before the stored procedure or trigger is called. However, if a query references a temporary table and two temporary tables with the same name exist at that time, it is not defined which table the query is resolved against.
Since at run time there will be two instances of #tmp in scope for the procedure, and it isn't defined which one will be picked, I don't think it can be considered a bug that the wrong one is chosen, although it would probably be preferable if the version created within in procedure had higher precedence.
I have not found much use for this, especially not in production scripts (although I can see how it might be useful in nested procedures), but sometimes in an ad hoc script, it is handy to encapsulate logic in a temporary procedure, and have this procedure modify a temp table. A very simple example might be:
CREATE TABLE #T (ID INT);
GO
CREATE PROCEDURE #Proc @val INT
AS
BEGIN
INSERT #T (ID) VALUES (@Val);
END
GO
EXECUTE #Proc 1;
EXECUTE #Proc 2;
EXECUTE #Proc 3;
SELECT ID FROM #T;
January 2, 2018 at 10:31 am
Nice interesting question. My natural reaction was to say the only message I would see would be an error message saying the permission to create a proc in tempdb wasn't available, because when I'm playing with things I tend not to have my playpen user endowed with wonderful permissions that I associate with a distinctly non-play role such as sysadmin. However, that particular error message wasn't an option, so I assumed that although the question didn't specify it the user concerned had some esoteric permissions (esoteric for an ordinary user, that is) so the proc would be created.
Oh what fun. Two temp tables, both created by the same thread in tempdb, both called #tmp. Which one will be seen at the crucial point? Gerneral answer:- the right one if you are lucky, but the wrong one when you are not. Interesting, but not useful. But clearly the stored proc which creates the wrong version is being run for the first time ever immediately before access is attempted, using just the table name, to the right version - so the version crated by that stored proc is the one that will will show up. That's the wrong version - so pick the appropriate error message from the list of exactly one which indicates the table definition doesn't fit the action.
Tom
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply