Recently, I had to rewrite this fairly large stored procedure. This stored proc aggregated a fairly large amount of data hence the reason we had to rewrite it. (Although, this was not the reason that was communicated to me about the rewrite. So, of course I went down the aggregated path then got into a heated argument about it. Talk to me people!) While rewriting it, it was clear that nested stored procedures would be the best approach to take. Of course while debugging it I ran into the “An INSERT EXEC statement cannot be nested” problem. While researching the issue I ran across two excellent articles written by Eralnd Sommarskog, a SQL Server MVP. The first article describes how to share data between stored procedures. The second, titled The Curse and Blessings of Dynamic SQL, discusses dynamic SQL in depth. My solution to the problem was to include the “INSERT INTO” in a dynamic SQL statement within the inner stored proc. This way the outer stored procedure can use the INSER INTO table EXEC sp_name syntax. It might not be the best way to handle the problem but as always time is of the essence. It’s now done and I’m interested in to see how well it performs.
Now Playing: Crash Rickshaw – Self Defeating Mind