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

Question

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

@DaleBurrell, actually when I run my query on the server side, everything works just find. — jreloz, Sep 23 '19 at 08:19
Michal is correct, you need a global temp table (`##`). A local temp table is automatically dropped at the end of the proc. — Dale K, Sep 23 '19 at 08:32
@Suraj S is correct, you should just select out the results in the stored proc and they will be returned to your c# layer. The nice thing about this is you can have multiple select statements and you get multiple tables in your result set. — Jonathan Freestone, Sep 23 '19 at 10:23

score 1 · Answer 1 · answered Sep 23 '19 at 09:25

1

if u want to use temp table data just select temp table data inside procedure ,i.e. select * from #LoanPriorProjected your procedure will return result as a table then use datatable to show this table data.

answered Sep 23 '19 at 09:25

Suraj S.

81
9

score -1 · Accepted Answer · answered Sep 23 '19 at 08:21

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.

##temp tables are not needed for your scenario, just return the results directly from the stored proc. This is lighter on the server, you are making two connections when one would do. — Jonathan Freestone, Sep 23 '19 at 10:31
@JonathanFreestone .NET uses conncetion pool, so it is not so expensive :) — Michał Turczyn, Sep 23 '19 at 11:34

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

2 Answers2