CODEWITHSUNDEEP
databaset-sql
ksh
ksh

Reputation: 3

How to add #temp tables

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.

Upvotes: 0

Views: 165

Answers (2)

Richard Marskell - Drackir
Richard Marskell - Drackir

Reputation: 13302

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
}

Upvotes: 0

Oded
Oded

Reputation: 499002

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
}

Upvotes: 1

Related Questions