Here is the situation:
Procedure 1 creates a temp table (#MYTABLE) and calls Procedure 2. Procedure 2 also tries to create #MYTABLE, with different columns. When Procedure 2 tries to insert data into #MYTABLE, an error happens complaining "Invalid column name". I have two questions about this:
1) Shouldn't the system complain when #MYTABLE is created inside Procedure 2? I understand why it can't object at compilation time, but at runtime I would expect an error.
2) Given that it doesn't complain about the creation, and in fact when you SELECT from #MYTABLE inside Procedure 2, you see the new column, why does it complain about the INSERT?
Below is the code. Uncommenting either INSERT statement will get the error.
(I know a lot of ways to fix this situation, so I don't need responses about that. I just want to understand what's happening.)
IF OBJECT_ID(N'dbo.MYPROC1', N'P') IS NOT NULL
DROP PROCEDURE dbo.MYPROC1;
GO
CREATE PROCEDURE dbo.MYPROC1
AS
CREATE TABLE dbo.#MYTABLE ( Name VARCHAR(256) );
SELECT
'DO NOTHING 1' AS TABLENAME;
EXEC dbo.MYPROC2;
GO
IF OBJECT_ID(N'dbo.MYPROC2', N'P') IS NOT NULL
DROP PROCEDURE dbo.MYPROC2;
GO
CREATE PROCEDURE dbo.MYPROC2
AS
SELECT
'INSIDE PROC 2 BEFOREHAND' AS TABLENAME
,*
FROM
dbo.#MYTABLE;
CREATE TABLE dbo.#MYTABLE
(
Name VARCHAR(256)
,LastName VARCHAR(256)
);
--INSERT INTO dbo.#MYTABLE
-- ( Name, LastName )
-- SELECT
-- 'BARACK'
-- ,'OBAMA';
SELECT
'INSIDE PROC 2 AFTERWARDS' AS TABLENAME
,*
FROM
dbo.#MYTABLE;
--INSERT INTO dbo.#MYTABLE
-- ( Name, LastName )
-- SELECT
-- 'BARACK'
-- ,'OBAMA';
SELECT
'DO NOTHING 2' AS TABLENAME;
GO
EXEC MYPROC1;
Yes, the temp table is in the scope of the connection, so the nested stored procedure (sp2) will have access to #temp table create in sp1. Yes, in SQL 2008 we have ability to pass a table valued parameter (TVP) as input to a function or stored procedure.
Temporary Tables: "A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table.
Stored procedures can reference temporary tables that are created during the current session. Within a stored procedure, you cannot create a temporary table, drop it, and then create a new temporary table with the same name.
From the Create Table documentation:
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. Nested stored procedures can also create temporary tables with the same name as a temporary table that was created by the stored procedure that called it. However, for modifications to resolve to the table that was created in the nested procedure, the table must have the same structure, with the same column names, as the table created in the calling procedure.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With