January 12, 2012 at 8:41 am
I'm having trouble understanding some of the Temp table documentation in the SQL BOL.
To quote
"A local temporary table created within a stored procedure or trigger can have the same name as a temporary table 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. Nested stored procedures can also create temporary tables with the same name as a temporary table created by the stored procedure that called it. All references to the table name in the nested stored procedure are resolved to the table created in the nested procedure, for example:"
So statement 1 is:
"A local temporary table created within a stored procedure or trigger can have the same name as a temporary table 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."
This tells me that a temp table created in a stored procedure can get confused with another temp table with the same name.
But then statement 2 is :
Nested stored procedures can also create temporary tables with the same name as a temporary table created by the stored procedure that called it. All references to the table name in the nested stored procedure are resolved to the table created in the nested procedure, for example: etc...
So this tells me that a temp table created in a stored procedure always references the one created in that proc.
Under what circumstances is statement 1 true - when can a stored procedure become confused between a temp table created in the proc and another existing temp table.
A small code example of where the problem could occur would be appreciated.
Thanks
January 12, 2012 at 8:56 am
You can call things besides stored procedures from within a stored procedure, so I imagine that's what they're talking about.
I've never deliberately tried to name something the same in a calling and sub-script.
Here's what I just tested:
CREATE PROC dbo.TempTableTest
AS
SET NOCOUNT ON;
CREATE TABLE #T (
ID INT);
DECLARE @Cmd VARCHAR(1000);
SET @Cmd = 'create table #T (ColA char(1)); select * from #T';
EXEC (@Cmd);
GO
EXEC dbo.TempTableTest;
GO 10
The final "GO 10" runs the test proc 10 times in a row. On my machine, it returned the second #T every time, but I'm assuming from the documentation that that's not guaranteed behavior. Could run 100 times correctly, and then do something odd on the 101st.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2012 at 9:08 am
Thanks. I was kind of thinking the same thing.
At my shop we only use stored procs [Edit: Meaning - without any dynamic SQL in them] . I'm trying to determine if there is any risk of local temp table name collision between any combination of stored proc call. According to the BOL, it should be aways safe, but I have a colleague who claims to have found problems with local temp tables created in procs either colliding or having bad data, and I am skeptical.
Any thoughts?
January 13, 2012 at 6:45 am
In production code, I always use meaningful names, and avoid collision explicitly. I've never tried using temp tables with the same names in the same scope. I just instinctively avoided it and never realized you could do so until you brought it up. Having a sub-proc create a temp table with the same name as one that's already in its scope because of creation in a parent-proc seems like a violation of the whole idea of object-naming in a scope. It would make debugging and refactoring more difficult, because you couldn't tell from an error message which version of the temp table was having a problem.
So, I really can't speak to it from experience. Just out of general policy of not having two objects with the same name in the same scope, I would avoid it. But that's personal preference, not something I can really prove is inherently better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply