Can I rollback Dynamic SQL in SQL Server / TSQL

Question

Can I run a dynamic sql in a transaction and roll back using EXEC:

exec('SELECT * FROM TableA; SELECT * FROM TableB;');

Put this in a Transaction and use the @@error after the exec statement to do rollbacks.

eg. Code

BEGIN TRANSACTION

   exec('SELECT * FROM TableA; SELECT * FROM TableB;');

   IF @@ERROR != 0
     BEGIN
       ROLLBACK TRANSACTION
       RETURN
     END
   ELSE
     COMMIT TRANSACTION

If there are n dynamic sql statements and the error occurs in n/2 will the first 1 to ((n/2) - 1) statements be rolled back

---

Questions about the first answer

@@Error won't pick up the error most likely Which means that it might not pick up the error, which means a transaction might commit? Which defeats the purpose

TRY/CATCH in SQL Server 2005+ Yes I am using SQL Server 2005 but haven't used the Try Catch before Would doing the below do the trick

BEGIN TRANSACTION 
   BEGIN TRY 
      exec('SELECT * FROM TableA; SELECT * FROM TableB;'); 
      COMMIT TRANSACTION 
   END TRY 
   BEGIN CATCH 
      ROLLBACK TRANSACTION 
   END CATCH

OR I looked at some more examples on the net

BEGIN TRY --Start the Try Block..
 BEGIN TRANSACTION -- Start the transaction..
  exec('SELECT * FROM TableA; SELECT * FROM TableB;');
 COMMIT TRAN -- Transaction Success!
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
      ROLLBACK TRAN --RollBack in case of Error
  RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1)
END CATCH

Answer 1

Yes. The TXNs belong to the current session/connection and dynamic SQL uses the same context.

However, @@ERROR won't pick up the error most likely: the status has to be checked immediately after the offending statement. I'd use TRY/CATCH, assuming SQL Server 2005+

Edit: The TRY/CATCH should work OK.

Answer 2

Don't take our word for it that try catch will work, test it yourself. Since this is dynamic sql the easiest thing to do is to make the first statement correct (and of course it mneeds to bean update,insert or delete or there is no need for atransaction) and then make a deliberate syntax error in the second statment. Then test that the update insert or delete in the first statment went through.

I also want to point out that dynamic sql as rule is a poor practice. Does this really need to be dynamic?