Capture effects of nested stored procedure without committing

• jonathanmarenus

  Mr or Mrs. 500

  Points: 508

  More actions

  June 28, 2018 at 12:53 pm

  #363741

  I have a situation where a nested stored procedure has a lot of code and many updates.  I only want to get one value that would be updated after the procedure runs, but without committing the procedure.  I basically want to see what the resulting value would be and assign that value in a single update statement within the outer procedure.  I thought about using a BEGIN TRAN....ROLLBACK, but is there any other way?

  Thanks.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 28, 2018 at 1:07 pm

  #1995720

  jonathanmarenus - Thursday, June 28, 2018 12:53 PM

  I have a situation where a nested stored procedure has a lot of code and many updates.  I only want to get one value that would be updated after the procedure runs, but without committing the procedure.  I basically want to see what the resulting value would be and assign that value in a single update statement within the outer procedure.  I thought about using a BEGIN TRAN....ROLLBACK, but is there any other way?

  Thanks.

  Is it a single value or a single column?  Either way you can use the BEGIN TRAN...ROLLBACK.  For the first, assign the result to a variable that is passed back as an output parameter.  If the latter, assign the values to a table variable, do the rollback, then write the results from the table variable to a permanent table to review after the procedure finishes.