Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Creating duplicate temp tables inside nested stored procedures

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;
like image 755
Daphne B Avatar asked Oct 28 '15 19:10

Daphne B


People also ask

Can we use temp table in nested stored procedure?

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.

Can we call temp table from another 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.

Can we use temp table in another stored procedure SQL Server?

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.


1 Answers

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.

like image 90
Joe Stefanelli Avatar answered Sep 18 '22 17:09

Joe Stefanelli