select data from #Temp table after #temp table create in another query in asp.net c#

Question

First i am execute procedure for create #temp table with data in first, then after i want #temp some column with other table column using joins. first query is execute after that on second query error occur (#temp object is invalid)

       if (con.State == ConnectionState.Closed)
        { con.Open(); }

        IsInTransaction = true;
        trans = con.BeginTransaction();


        da = new SqlDataAdapter("Execute SP_Statement_Temp", con);
        da.SelectCommand.Transaction = trans;
        DataTable DatTemp = new DataTable();
        da.Fill(DatTemp);
       SelectString = "Select Distinct #temp.IdentityID, TblMasterTypeOfIdentity.TypeOfIdentity,TblIdentity.IdentityName, '' As 'Opening Balance' , '' As 'Closing Balance'  from #temp inner join TblIdentity on TblIdentity.IdentityID=#temp.IdentityID inner join TblMasterTypeOfIdentity on TblMasterTypeOfIdentity.TypeOfIdentityID=#temp.TypeOfIdentityID";    
        CmdString = SelectString + " " + WhereString + " " + OrderBy;

        da = new SqlDataAdapter(CmdString, con);
        da.SelectCommand.Transaction = trans;
        DataTable datDetail = new DataTable();


        da.Fill(datDetail);
        trans.Commit();
        IsInTransaction = false;
        con.Close();

Possible duplicate of [Does SqlDataAdapter open its own connection?](https://stackoverflow.com/questions/40634670/does-sqldataadapter-open-its-own-connection) — Crowcoder, Mar 18 '18 at 19:54
There is an answer that is good, but ask yourself why you need to create a temp table in a stored procedure to select from in another statement. — , Mar 18 '18 at 21:43

score 3 · Accepted Answer · answered Mar 18 '18 at 19:53

That's going to be because the #temp table is dropped immediately after creating SP.

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. The table cannot be referenced by the process that called the stored procedure that created the table.

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql

score 2 · Answer 2 · answered Mar 18 '18 at 19:59

If you want to create a temporary table you can use across connections, you can use a double hash (##) instead of a single (#). This creates a global temporary variable, as opposed to a local temporary variable. So, if you change the SQL inside Execute SP_Statement_Temp to create a temp variable called ##temp instead of #temp, you should be able to use that in the SQL.

This has been asked before, see e.g.

Local and global temporary tables in SQL Server

select data from #Temp table after #temp table create in another query in asp.net c#

2 Answers2