CODEWITHSUNDEEP
c#asp.netsql-server
GCDX
GCDX

Reputation: 85

ASP.NET: Storing sessions with #temp tables to run other store procedures on

The project I have been working on can be broken into two different stored procedures.

  1. 1st Stored procedure: It is to join a few tables together into a temp table.
  2. 2nd Stored procedure: This sp is edited/written based on the contents of a table that has CRUD, and it works on the temp table generated in the first stored procedure.

I am trying to execute the first stored procedure and store the temp table in a session and then run the second stored procedure manually obtain another table that uses.

-EDIT-

SP1 takes about 30s to set up a temp table by joining many tables, and I want to use the temp table from SP1 not just for SP2 but many different SPs.

I'm not sure if this is possible?

Upvotes: 0

Views: 708

Answers (1)

banu saladi
banu saladi

Reputation: 66

No it's not possible as the local temporary tables have scope to only current session.

You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions [Source :MSDN]

If you are trying to create this for a web application I suggest you to create Stored Procedure (which wraps both stored procedures execution) and the creation of temp table should be at the outer stored procedure. Then it will be available to both the stored procedures that are invoked from this procedure.

Going with global temporary tables is also not a reliable solution.

Upvotes: 3

Related Questions