воскресенье, 16 января 2011 г.

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


Hi,
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;
GOBEGIN TRANSACTION;
BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRYBEGIN 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.
Hope this helps but please let me know if you need more details.

Комментариев нет: