Why cannot create global temporary table or trunca

Question

I spent whole days and hours to investigate why cannot create temporary table when I call a stored procedure from C# code.

Let's assume a piece of C# code:

public void GetDataByFilter(string item, string filter ...) {
   ... 
   // provide stored procedure in new SqlCommand object
   sqlCommand.CommandType = CommandType.StoredProcedure;

   sqlCommand.Parameters.Add("@val1", aValue1);
   sqlCommand.Parameters.Add("@val2", aValue2);
   ...
   while(sqlDataReader.Read()) { /* process info here */ }
}

and my stored procedure

ALTER PROCEDURE [dbo].[aProcedure]
  @val1 VARCHAR(255),
  @val2 VARCHAR(255),
  ...
AS 
BEGIN
  IF OBJECT_ID(N'tempdb..##aTempTable') IS NULL
    CREATE TABLE tempdb..##aTempTable (
          id1 INT,
              id2 INT,
              id3 VARCHAR(255),
              ...
    )

ELSE 
    TRUNCATE TABLE tempdb..##aTempTable

    -- insert information into this table
    SELECT @paramList = ' /*list of parameters*/';

    EXEC sp_executesql @sql, @paramList, ... parameters
END

In C# code, in another function, I provided

sqlCommand.CommandText = "SELECT * FROM tempdb..##aTempTable";

But exception was thrown that tempdb..##aTemTable doesn't exists.

If I execute stored procedure inside SSMS (Sql Server Management Studio) then the table is created.

The problem is also for TRUNCATE TABLE operation not only for CREATE TABLE.

I need temporary global table for to be used into different stored procedures accordingly with It is possible for created temporary table by a stored procedure to be used in another stored procedure?.

Any solution ?

Answer 1

I believe that while it is possible to do what you’re describing, it is not too practical, as it presumes a lot of tricky timing: a session opens connection A and runs the proc to create the ##temp table, and that session keeps the connection open for as long as connections B, C, etc. need to be able to access the temp table.

Yould’ be better off with a solution using a “permanent” table, where a row is added with a unique identifier (int or GUID) for every “session”, that value gets returned by the initialization call (or can otherwise be retrieved or identified), and used by all subsequent calls for the session.

Answer 2

As I alluded to in my previous answer (but since you hadn't explained your scenario at the time, I didn't spell out) is that any temporary table created in one scope is accessible to any nested scope.

So, in your C# you can run the following queries:

CREATE TABLE #aTempTable (
      id1 INT,
          id2 INT,
          id3 VARCHAR(255),
          ...
);

then:

[dbo].[aProcedure]

then:

[dbo].[bProcedure]

then:

SELECT * from #aTempTable

Provided that all of these queries are run using the same SqlConnection object, and that the connection is kept open throughout. The first and fourth queries merely run in the scope of the connection. The two stored procedure calls run inside nested scopes.

There's no need for a global temp table. aProcedure can simply access #aTempTable (and no need to prefix any reference to it with tempdb.. anywhere.