[SOLVED] How to retrieve #temptable data that was created inside a stored procedure and SqlCommand?

How to retrieve #temptable data that was created inside a stored procedure and SqlCommand?

I've been reading a lot of answers here on how to reuse SqlCommand but none of them are the answer to this problem. Basically I have stored procedure that creates a #temptable.

Something like this:

CREATE PROCEDURE [dbo].[ProjectPriorInterestIncome]
    @selectedDate Date,
    @EndDay Date
AS
BEGIN
    CREATE TABLE #LoanPriorProjected
    (
      Colums here....
    )
END

In my .Net I have a SqlCommand that executes the stored procedure. Something like this:

Using cmd As New SqlCommand("ProjectPriorInterestIncome", SQLConn)
   cmd.CommandType = CommandType.StoredProcedure
   cmd.Parameters.AddWithValue("@SelectedDate", frmDefault.dtDate.Value)
   cmd.Parameters.AddWithValue("@EndDay", Format(GetLastDayofMonth(frmDefault.dtDate.Value), "12/31/yyyy"))
   cmd.ExecuteNonQuery()  

   'Executing a select query from #temptable'
   cmd.CommandText = "SELECT * FROM #LoanPriorProjected"
   cmd.CommandType = CommandType.Text
   cmd.ExecuteNonQuery()
End Using

Now, when I try to execute a select query from the #LoanPriorProjected table, It says

Invalid object name '#LoanPriorProjected'.

Solution

Temporary tables are... temporary - they exist within one connection. So when executing procedure, table is created, then command is completed and temporary table is gone.

You can make "more global" temporary table by using double hash ##temptable.

For better explanation refer to this:

The classic temporary table comes in two flavors, the Global, or shareable, temporary table, prefixed by ‘##’, and the local temporary table, whose name is prefixed with ‘#’.The local temporary tables are less like normal tables than the Global temporary tables: You cannot create views on them, or associate triggers with them. It is a bit tricky to work out which process, session or procedure created them. We’ll give you a bit of help with that later. Most importantly, they are more secure than a global temporary table as only the owning process can see it.