Best way to work with transactions in MS SQL Server Management Studio

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

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?)

Solution 1:

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.

For example,

Begin Transaction

         -Do some T-SQL queries here.

Rollback transaction -- OR commit transaction

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.

For example:

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.

Solution 2:

I want to add a point that you can also (and should if what you are writing is complex) add a test variable to rollback if you are in test mode. Then you can execute the whole thing at once. Often I also add code to see the before and after results of various operations especially if it is a complex script.

Example below:

USE AdventureWorks;
GO
DECLARE @TEST INT = 1--1 is test mode, use zero when you are ready to execute
BEGIN TRANSACTION;

BEGIN TRY
     IF @TEST= 1
        BEGIN
            SELECT *FROM Production.Product
                WHERE ProductID = 980;
        END    
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;

     IF @TEST= 1
        BEGIN
            SELECT *FROM Production.Product
                WHERE ProductID = 980;
            IF @@TRANCOUNT > 0
                ROLLBACK TRANSACTION;
        END    
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 AND @TEST = 0
    COMMIT TRANSACTION;
GO