SQL Server / TSQL中是否可以回滚动态SQL？

Question

我可以在事务中运行动态SQL语句并使用EXEC回滚吗：

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

将此放入事务中，并在执行语句后使用@@error执行回滚操作。

例如，代码：

BEGIN TRANSACTION

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

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

如果有n个动态SQL语句，并且错误发生在n/2，则前1到((n/2)-1)个语句会回滚。

---

关于第一个答案的问题

@@Error可能不会捕获错误，这意味着它可能无法捕获错误，从而事务可能会提交？这就失去了目的。

在SQL Server 2005+中使用TRY/CATCH

是的，我正在使用SQL Server 2005，但以前没有使用过Try Catch。做以下操作是否可以解决问题？

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

我查看了网上的更多示例

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

- soldieraman

Answer 1

是的。TXNs属于当前会话/连接，动态SQL使用相同的上下文。

但是，@@ERROR可能无法捕获错误的情况：必须在有错误的语句之后立即检查状态。 我建议使用TRY/CATCH，假设使用SQL Server 2005+。

编辑：TRY/CATCH应该可以正常工作。

- gbn

---

在你的回答之后，我有一个延伸问题。关于TRY/Catch。 - soldieraman

Answer 2

不要只听我们说捕获错误会起作用，自己测试一下。由于这是动态 SQL，最简单的方法是使第一个语句正确（当然，它必须是更新、插入或删除，否则就不需要事务），然后在第二个语句中故意引发语法错误。然后测试第一个语句中的更新、插入或删除是否成功。

我还想指出，作为规则，动态 SQL 是一种糟糕的实践。这真的需要是动态的吗？

- HLGEM

---

我需要从应用程序本身运行SQL语句。也就是说，用户可以进入ASP.NET应用程序，获取一个框，并可以从那里运行SQL语句。 - soldieraman

@soldieraman：哇，你应该意识到这个应用程序的“功能”有大量的安全隐患吧？我只希望在运行用户输入之前，你至少能够转义或清理它，以避免对数据库造成影响。 - Oliver