CODEWITHSUNDEEP
c#sql.netsql-servervisual-studio
Alex Gordon
Alex Gordon

Reputation: 60691

Stored procedures do not see temp table created in the same connection

Why does my temp table get dropped?

Currently, we have:

var listOfSqlCommands = new List<SqlCommand>();

It gets populated with a SqlCommand with CommandType = CommandType.StoredProcedure;

Background information:

Full code:

var listOfSqlCommands = new List<SqlCommand>();

using (var O0aAConnection = new SqlConnection(connectionString))
{
    try
    {
        O0aAConnection.Open();

        foreach (DataTable dt in ds.Tables)
        {
            EntityName = StringDefaultIfNull(syncConfig.Root.XPathSelectElement("./entity[@name='" + dt.TableName + "']"), "O0aATableName").Substring(3);

            var O0aACommand = new SqlCommand();

            O0aACommand.CommandType = CommandType.StoredProcedure;
            O0aACommand.CommandText = "O0aA42_P_" + EntityName + "Sync_OfferingPublish";

            var entityNameParam = new SqlParameter("@EntityName", dt.TableName);

            O0aACommand.Parameters.Clear();
            O0aACommand.Parameters.Add(entityNameParam);

            var tblParam = new SqlParameter("@O42_" + EntityName, SqlDbType.Structured);
            tblParam.Value = dt;
            O0aACommand.Parameters.Add(tblParam);

            O0aACommand.Connection = O0aAConnection;

            listOfSqlCommands.Add(O0aACommand);
        }

        using (var createTempTableCommand = new SqlCommand())
        {
            createTempTableCommand.CommandText = "CreateTempTableSproc";
            createTempTableCommand.CommandType = CommandType.StoredProcedure;
            createTempTableCommand.Connection = O0aAConnection;

            createTempTableCommand.ExecuteNonQuery();
        }

        // the commands here are NOT seeing the temp table that was created above!!
        foreach (var command in listOfSqlCommands)
        {
            using (var da = new SqlDataAdapter(command))
            {
                da.Fill(dtResults);
            }
        }
    }                
    finally
    {
        O0aAConnection.Close();
    }            
}

Why does my temp table get dropped?

Upvotes: 1

Views: 118

Answers (2)

Hogan
Hogan

Reputation: 70513

Temporary tables (both local and global) only exist in the context of the session OR the stored procedure in which they were created.

You have to not use a stored procedure to create it.

EXEC will also create a context for temporary tables that will go away on return from the EXEC if they are created within the EXEC. Code called by EXEC will see existing temp tables.

Upvotes: 3

John Cappelletti
John Cappelletti

Reputation: 81930

You can CHAIN the results of a stored procedure

For example:

CREATE PROCEDURE [dbo].[prcCall1]
As
Begin
    Select Val=1 into #Temp
    Exec [prcCall2]
    Select * from #Temp
End

Go

CREATE PROCEDURE [dbo].[prcCall2]
As
Begin
    Update #Temp Set Val=Val+1
    --Select * from #Temp
End

Go

Exec [dbo].[prcCall1]

Returns

Val
2

Upvotes: 1

Related Questions