在 MS SQL Server Management Studio 中处理事务的最佳方式 [英] Best way to work with transactions in MS SQL Server Management Studio

问题描述

假设我有一个语法和语义正确的 SQL 语句，因此它可以执行.

Let's say I have an SQL statement that's syntactically and semantically correct so it executes.

在 Management Studio(或任何其他查询工具)中，我如何测试 SQL 语句，如果我发现它们破坏了某些内容，请回滚(在单独的查询中?)

In Management Studio (or any other query tool) how can I test SQL statements, and if I notice that they broke something, rollback (in a separate query?)

推荐答案

最简单的做法是将代码封装在一个事务中，然后逐行执行每批 T-SQL 代码.

The easisest thing to do is to wrap your code in a transaction, and then execute each batch of T-SQL code line by line.

例如

Begin Transaction

         -Do some T-SQL queries here.

Rollback transaction -- OR commit transaction

如果您想合并错误处理，您可以使用 TRY...CATCH BLOCK 来实现.如果发生错误，您可以回滚 catch 块中的事务.

If you want to incorporate error handling you can do so by using a TRY...CATCH BLOCK. Should an error occur you can then rollback the tranasction within the catch block.

例如:

USE AdventureWorks;
GO
BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

查看以下链接了解更多详情.

See the following link for more details.

http://msdn.microsoft.com/en-us/library/ms175976.aspx

希望这会有所帮助，但如果您需要更多详细信息，请告诉我.

Hope this helps but please let me know if you need more details.

这篇关于在 MS SQL Server Management Studio 中处理事务的最佳方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！