Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rlull
Starting Member
39 Posts |
 Posted - 2011-04-18 : 18:31:15
|
| I am working with an existing stored procedure. It currently returns 2 result sets. I now need to extend it and return up to 5 result sets. The number of result sets is determined by some logic within the proc and will vary each time it's called. The current logic uses temp tables to return the data. Is there a way to dynamically create up to 3 more temp tables with names that are created on the fly? Perhaps there is a better solution to return a variable number of result sets? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rlull
Starting Member
39 Posts |
Posted - 2011-04-18 : 23:06:11
|
| Thanks, Tara. That makes sense but what if I do go beyond 5 and need to make it dynamic? Is there a better way to structure the logic in the proc to use something other than temp tables? |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-04-18 : 23:14:33
|
| You can have many more temp tables, just give them unique names within the proc.CODO ERGO SUM |
|
|
|
rlull
Starting Member
39 Posts |
Posted - 2011-04-18 : 23:29:32
|
| Thanks, Michael - but how do I create them dynamically with unique names? For example, one time the proc is called, I may need 5 tables. Another time I may need only 3. Next time, maybe 7. I don't want to hard code all the names ahead of time. I only want to create as many as I need each time the proc is called. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rlull
Starting Member
39 Posts |
Posted - 2011-04-19 : 11:29:21
|
| It's definitely a design issue. Here's the scenario: The proc is called and TempTable1 is created from a subset of a view. TempTable2 is created from a subset of TempTable1. Based on the number of rows (usually 3 but theoretically could be up to 10) in yet another table we'll call OutsideTable, I need to create the corresponding number of temp tables. For example, if 3 rows are returned, I need to create an additional 3 temp tables that are a subset of TempTable1. If 5 rows are returned, I need to create an additional 5 temp tables. So in the end, the proc will return a result set containing the results from TempTable1, TempTable2 and additional temp tables for however many rows are returned from OutsideTable. Does that help? |
|
|
|
rlull
Starting Member
39 Posts |
Posted - 2011-04-19 : 11:46:30
|
| Nevermind, I figured it out. I was able to redesign the proc in such a way as to avoid additional temp tables. Thanks for your time. |
|
|
|
|
|
|
|
|
Dynamically create temp tables with unique names