For my education, I EXEC a dynamic sql that is embedded in a BEGIN TRAN ... COMMIT TRAN. A simple example and assume the var have been defined:
SET @sqlcmd = 'INSERT INTO dbo.TARGET_TBL'
+ ' (colA,colB)'
+ ' SELECT Acol, Bcol FROM dbo.SOURCVE_TBL'
SET @theErrNbr = @@ERROR
@sqlcmd has the name of the target table in a string which the outer layer, BEGIN TRAN ... COMMIT TRAN may not see.
Question1: Will COMMIT TRAN know to commit the changes to the SOURCE_TBL? (executed dynamically). What about ROLLBACK?
Question2: I will receive the @@ERROR number if it fails?
Excuse my unawareness and MANY THANKS.