How to add #temp tables

Question

I have formed few temp tables in my query (TSQL) like - #temphold1, #temphold2, #temphold3..... #temphold10. Each temp table has different schema (different columns) each derived by grouping data from different tables with specific conditions. I need to determine a way to carry all these temp tables to the User Interface and display each table seperately. Is there a way I can add all temp tables with specific indexer that i can retrieve at the User Interface.

thanks for any reply.

Answer 1

No, there is no such indexer.

However, SQL Server and ADO.NET support returning multiple result sets by selecting each table in turn.

See this howto on MSDN (How To Handle Multiple Results by Using the DataReader in Visual C# .NET).

So, in your stored procedure:

-- after populating your temp tables:
SELECT * FROM #table1
SELECT * FROM #table2
SELECT * FROM #table3

In essence, after reading the first recordset, you call NextResult() on the DataReader in order to get the results of the next select:

while(dr.Read())
{
   // process data from #table1
}

dr.NextResult();

while(dr.Read())
{
   // process data from #table2
}

dr.NextResult();

while(dr.Read())
{
   // process data from #table3
}

Answer 2

If you're returning results to C#, you can do it with a DataAdapter like this:

using (SqlConnection conn = new SqlConnection("your connection string")) {
    SqlParameter[] sqlParams = new SqlParameter[] {
        new SqlParameter("@param1",10),
        new SqlParameter("@param2","test")
    };
    conn.Open();
    SqlDataAdapter sa = new SqlDataAdapter("spStoredProcName", conn);
    sa.SelectCommand.CommandType = CommandType.StoredProcedure;
    sa.SelectCommand.Parameters.AddRange(sqlParams);
    DataSet ds = new DataSet();
    sa.Fill(ds);
    //ds.Tables[0] == first table
    //ds.Tables[1] == second table
    //... etc.
    //Do whatever you need to here
}