This is my situation. I am in the process of automating several warehouse transactions that currently are done via hand scanner + proprietary DLL that in turn calls a series of stored procedures. The goal is to allow another layer of stored procedures to call the existing ones instead of using the DLL so I can have the warehouse transactions executed from a PLC. I cannot alter the original procedure as the hand scanners will still be in use and will be the failback if the automation runs into an unrecoverable error.
The existing Procedure that currently handles placing the box on a skid is roughly 300 lines of very convoluted logic that handles several layers of validation and insert/updates about 3-5 tables. Inside the execution phases of the procedure it uses a transaction and rollback for any errors that occur. In the event of a validation error or insertion/update error it returns two text fields, in the event of successful completion it returns 4 text fields. It appears there is hardcoded program logic in the proprietary DLL that relies on these fields to be presented in this fashion.
The automated procedure I am writing calls this procedure with an INSERT INTO to capture the result so I can include the error message in my own return value (integer error code, text description of error).
Unfortunately I receive the error message that I cannot use the ROLLBACK statement within an INSERT-EXEC statement. From the little amount of research I have done so far, it appears my only option will be to either:
a) Change my entire error handling scheme to transparently pass their results back
b) Duplicate and modify their procedure
c) rewrite the critical portions of their procedure as a new one
My hope is that someone will have a better suggestion on how to handle the INSERT-Exec/Rollback situation.
I would post the procedures, but don't believe they are necessary for this question (and wouldn't want to submit you guys to the fun logic that starts with "If Not Exists (Select Null From" that I am working with.
-T
The existing Procedure that currently handles placing the box on a skid is roughly 300 lines of very convoluted logic that handles several layers of validation and insert/updates about 3-5 tables. Inside the execution phases of the procedure it uses a transaction and rollback for any errors that occur. In the event of a validation error or insertion/update error it returns two text fields, in the event of successful completion it returns 4 text fields. It appears there is hardcoded program logic in the proprietary DLL that relies on these fields to be presented in this fashion.
The automated procedure I am writing calls this procedure with an INSERT INTO to capture the result so I can include the error message in my own return value (integer error code, text description of error).
Unfortunately I receive the error message that I cannot use the ROLLBACK statement within an INSERT-EXEC statement. From the little amount of research I have done so far, it appears my only option will be to either:
a) Change my entire error handling scheme to transparently pass their results back
b) Duplicate and modify their procedure
c) rewrite the critical portions of their procedure as a new one
My hope is that someone will have a better suggestion on how to handle the INSERT-Exec/Rollback situation.
I would post the procedures, but don't believe they are necessary for this question (and wouldn't want to submit you guys to the fun logic that starts with "If Not Exists (Select Null From" that I am working with.
-T