SQL Server stored procedure restore my records if Insert failed

sql, sql-server, sql-server-2008, t-sql

Solution

You can put your two statements into a `TRY....CATCH` block and only commit if both statements succeed:

BEGIN TRANSACTION
BEGIN TRY
    DELETE FROM dbo.MYTABLE WHERE ID=@ID;

    INSERT INTO dbo.MYTABLE (ID, NAME)
       SELECT @ID, NAME

    -- COMMIT only if both DELETE and INSERT worked ....
    COMMIT TRANSACTION
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

    -- ROLLBACK if either DELETE and INSERT failed ....
    ROLLBACK TRANSACTION
END CATCH

Problem

I want to know if there is a way to state rollback the delete if I can not insert. Please advice. Something like below. ``` BEGIN TRAN Delete from MYTABLE where ID=@ID; INSERT INTO MYTABLE (ID, NAME) SELECT @ID, NAME COMMIT ```

Original source