0

I need to pass data among a list of stored procedures. The way this needs to be done is by using a local temp table. 

var createTempTableCommand = new SqlCommand("Create table #mytemptable ....", myconnection);

using (SqlDataReader dr = createTempTableCommand.ExecuteReader()) 
{
   // ...
}

foreach(command in listOfSqlCommands)
{
    using (SqlDataReader dr = command.ExecuteReader())
    {
        dtResults.Load(dr);
    }
}

How do I ensure that the temp table that has been created #mytemptable remains intact during the foreach iteration?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062
  • Did you execute that command? Until you execute there is no temp table – Steve Nov 15 '16 at 20:22
  • 2
    A temp table will exist for the entire time a given connection is open. If you don't close your connection the temp table will persist. That being said, I think there are likely some better ways to accomplish what you are trying to do here but it would require a lot more information to offer any details. – Sean Lange Nov 15 '16 at 20:23
  • Also, make sure you're not using any connection pooling, or if you are, that the whole lot gets executed with the same connection (perhaps by doing it in a transaction). – Jiri Tousek Nov 15 '16 at 20:31
  • @steve, thank you i've updated it, i forgot to show that i am indeed executing the first command – Alex Gordon Nov 15 '16 at 20:41
  • Don't use a *temporary* table. Create a "real" table with some specific naming convention that you manage and delete/write over as needed. – Gordon Linoff Nov 15 '16 at 20:55
  • @GordonLinoff why wouldnt you use a temp table for this? – Alex Gordon Nov 15 '16 at 21:52
  • @MeggieLuski Why do you need a temp table? – William Xifaras Nov 15 '16 at 22:04
  • @WilliamXifaras because this entire process can be called dozens of times per second, and each occurrence of this process needs its own table of data that is shared among the stored procedures – Alex Gordon Nov 15 '16 at 22:17
  • @MeggieLuski Got it. You can use a temp table for this, but I'd also read up on some of the other options. https://stackoverflow.com/questions/469062/how-to-share-data-between-stored-procedures – William Xifaras Nov 15 '16 at 22:23

0 Answers0