August 19, 2018 at 7:48 am
I have a requirement to get data from a deep chain of procs. However, there is already an "INSERT EXEC" statement in one of the deeper procs.
I am looking for options to overcome the error generated.
So far, I have come up with 2 options.
August 21, 2018 at 6:25 am
Be careful about changing functionality somewhere deep in a nest of such daisy-chained stored procedures. Once you have a nest like that, you may find you don't know that any of a number of those procedures are used elsewhere for their functionality, and a change to this particular one takes out that process(es?). I say this because the same kind of thinking that leads to such nests of stored procedures typically leads to re-use of those procedures for other things entirely independent of the nest portion you are dependent on. This is an unfortunately all too common problem, and it can ONLY be solved by tearing down the mess and starting over, and isolating functionality to avoid dependency. Typically, nested stored procedures are rather more difficult to troubleshoot where things are going wrong when a problem occurs, as opposed to allowing oneself to actually repeat the same code in a second stored procedure (or more). Code re-use isn't necessarily as critical as some folks think it is. Often, if you have that scenario staring you in the face, you may not actually have an optimal design/architecture. Something is probably missing from the design somewhere... And often, that's because something that probably belongs in the application is in the database, and/or vice versa.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 21, 2018 at 6:34 am
sgmunson - Tuesday, August 21, 2018 6:25 AMBe careful about changing functionality somewhere deep in a nest of such daisy-chained stored procedures. Once you have a nest like that, you may find you don't know that any of a number of those procedures are used elsewhere for their functionality, and a change to this particular one takes out that process(es?). I say this because the same kind of thinking that leads to such nests of stored procedures typically leads to re-use of those procedures for other things entirely independent of the nest portion you are dependent on. This is an unfortunately all too common problem, and it can ONLY be solved by tearing down the mess and starting over, and isolating functionality to avoid dependency. Typically, nested stored procedures are rather more difficult to troubleshoot where things are going wrong when a problem occurs, as opposed to allowing oneself to actually repeat the same code in a second stored procedure (or more). Code re-use isn't necessarily as critical as some folks think it is. Often, if you have that scenario staring you in the face, you may not actually have an optimal design/architecture. Something is probably missing from the design somewhere... And often, that's because something that probably belongs in the application is in the database, and/or vice versa.
Thanks Steve.
I am dealing with a set of legacy apps with more than 20 years of plasters on bullet holes.
Fortunately, as replace older apps with newer ones, we generally make the necessary changes along the way. But for existing apps, all changes need to be backward compatible.
August 21, 2018 at 6:43 am
DesNorton - Tuesday, August 21, 2018 6:34 AMThanks Steve.I am dealing with a set of legacy apps with more than 20 years of plasters on bullet holes.
Fortunately, as replace older apps with newer ones, we generally make the necessary changes along the way. But for existing apps, all changes need to be backward compatible.
Understand, but just don't be too surprised that when you add spackle to the crumbling bullet hole plaster that's already there, you end up collapsing the bullet hole due to the weight of the spackle, and you end up with a worse problem... If you can verify that the targeted stored procedure can actually be changed without causing a chain of disaster, then go ahead and create a temp table within that sproc. You may be able to just add an OUTPUT clause to the final query to do the job. Of course, test, test, and re-test, then wash, rinse, and repeat....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply