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