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 |
|
groston
Yak Posting Veteran
61 Posts |
 Posted - 2006-01-19 : 11:34:15
|
| Hoping for more good suggestions from this group!I have the following (SQL Server 2000 SP3):sp1: SELECT <stuff> FROM <tables>sp2: CREATE TABLE #t2 INSERT #t2 EXEC sp1 <variables> SELECT <stuff> FROM #t2, <other tables>sp3: CREATE TABLE #t3 INSERT #t3 EXEC sp2 <variables> SELECT <stuff> FROM #t3, <other tables>The problem (obviously?) is that I get the error: An INSERT EXEC statement cannot be nested.when I try running sp3.One thought was to replace the temp table in sp2 with a table variable. This is a non starter because you cannot INSERT EXEC into a table variable. I thought about the possibility of using views, but the parameters that get passed in are key, and views are essentially parameterless.My current work-around (which is truly awful) is to duplicate the code in sp2 inside sp3, thus eliminating the problem. There must be a better way - any suggestions? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-01-19 : 11:58:07
|
| Create a temp table in the calling stored procedure, and have the stored procedure it is calling insert data into that temp table.CODO ERGO SUM |
 |
|
|
groston
Yak Posting Veteran
61 Posts |
Posted - 2006-01-19 : 12:34:27
|
| Michael,It sounds like you know exactly what needs to be done, but I do not quite understand. Are you suggesting that if in sp3, I do:CREATE TABLE #t2That in sp2, I can reference this table? It seems to me that this would cause a 'compile' error as sp2 won't have the definition of #t2. Also, how to I then handle the case of sp2 being called directly if the table is defined in another sp?Would you kindly point me to some more information related to your suggested solution.Thanks! |
|
|
|
|
|
|
Problem with nested INSERT EXEC