mtillman-921105 (1/12/2012)
This is an older thread, but I'll try to give an example and explain more.If you have one temp table named #Summary in the "parent" or calling procedure, then it is not a good idea to have a temp table with the same name in the "child" procedure (or the procedure which is being called).
Maybe books online is referring to regular, non-temp, tables. But experience shows that having the same table names is potentially an issue. At the very least, I would avoid that just to be safe.
I can't reproduce what you're talking about, and BOL doesn't appear to agree with your statement. Do you have a reproducible script?
Here is what I tried: -
BEGIN TRAN
GO
CREATE PROCEDURE Child
AS
BEGIN
CREATE TABLE #test (ID INT)
INSERT INTO #test
SELECT 2
SELECT *, 'Child'
FROM #test
END
GO
CREATE PROCEDURE Parent
AS
BEGIN
CREATE TABLE #test (somethingElse VARCHAR(100))
INSERT INTO #test
SELECT 'One'
EXECUTE Child
INSERT INTO #test
SELECT 'Two'
SELECT *, 'Parent'
FROM #test
END
GO
DECLARE @i INT
SET @i = 0
CREATE TABLE #test (somethingDifferent INT)
WHILE @i < 3
BEGIN
INSERT INTO #test
SELECT @i
SELECT *, 'NonSproc' FROM #test
EXECUTE Parent
SELECT *, 'NonSproc' FROM #test
SET @i = @i + 1
END
ROLLBACK
This executes with no problem, producing the results expected.