Why Cannot Create Global Temporary Table Or Truncate It When Call A Stored Procedure From C# Code?

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 GetDataByFil

Solution 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.

Solution 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:

CREATETABLE #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.