locked
how to write best transaction so that I can rollback? RRS feed

  • Question

  • User-804681621 posted
    I have created the stores procedure and I am using Sal server 2012.

    I am not can't rollback after intercourse problem. Below is my Sql.

    Begin transaction
    Begin try

    Select * into table1 from table3;

    Select * from table2;

    End try
    Begin catch
    Rollback;
    Print error_message();
    return -1;
    End catch
    Commit;
    Return 0;--success
    End


    Actually, the table2 does not exist in database. But when I run the stores pro.
    or will not fall into catch session. And can't rollback? Why?
    Monday, March 21, 2016 3:45 AM

Answers

  • User-219423983 posted

    Hi pn_nq,

    I think you could first have a look at the section “Errors Unaffected by a TRY…CATCH Construct” in below article. As it says, your above code would not be caught by the TRY…CATCH construct, but is caught by the CATCH block when the same SELECT statement is executed inside a stored procedure.

    https://technet.microsoft.com/en-us/library/ms175976(v=sql.110).aspx

    The following code I have tested and it works on my client. So, you could use try-catch to handler the error “Invalid object name 'table name'” for the stored procedure, not inside the SP.

    -- Verify that the stored procedure does not exist.
    IF OBJECT_ID ( N'usp_ExampleProc', N'P' ) IS NOT NULL 
        DROP PROCEDURE usp_ExampleProc;
    GO
    
    -- Create a stored procedure that will cause an 
    -- object resolution error.
    CREATE PROCEDURE usp_ExampleProc
    AS
        SELECT * FROM NonexistentTable;
    GO
    
    BEGIN TRY
        EXECUTE usp_ExampleProc;
    END TRY
    BEGIN CATCH
        SELECT 
            ERROR_NUMBER() AS ErrorNumber
            ,ERROR_MESSAGE() AS ErrorMessage;
    END CATCH;
    

    Besides, you could refer to below thread to first check whether the table is exists or not.

    http://stackoverflow.com/questions/10702997/raise-error-if-table-does-not-exists-in-database

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 21, 2016 11:01 AM
  • User-62323503 posted

    Would suggest to first understand the basics of Transactions and how it works.

    http://www.itdeveloperzone.com/2013/07/transaction-in-sql-server.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 22, 2016 5:22 AM

All replies

  • User-804681621 posted
    Anyone helps?
    Monday, March 21, 2016 6:21 AM
  • User-804681621 posted
    Anyone helps? Give reference code for me if the table does not exists and then rollback so that three is no data insert to table1.
    Monday, March 21, 2016 6:22 AM
  • User-219423983 posted

    Hi pn_nq,

    I think you could first have a look at the section “Errors Unaffected by a TRY…CATCH Construct” in below article. As it says, your above code would not be caught by the TRY…CATCH construct, but is caught by the CATCH block when the same SELECT statement is executed inside a stored procedure.

    https://technet.microsoft.com/en-us/library/ms175976(v=sql.110).aspx

    The following code I have tested and it works on my client. So, you could use try-catch to handler the error “Invalid object name 'table name'” for the stored procedure, not inside the SP.

    -- Verify that the stored procedure does not exist.
    IF OBJECT_ID ( N'usp_ExampleProc', N'P' ) IS NOT NULL 
        DROP PROCEDURE usp_ExampleProc;
    GO
    
    -- Create a stored procedure that will cause an 
    -- object resolution error.
    CREATE PROCEDURE usp_ExampleProc
    AS
        SELECT * FROM NonexistentTable;
    GO
    
    BEGIN TRY
        EXECUTE usp_ExampleProc;
    END TRY
    BEGIN CATCH
        SELECT 
            ERROR_NUMBER() AS ErrorNumber
            ,ERROR_MESSAGE() AS ErrorMessage;
    END CATCH;
    

    Besides, you could refer to below thread to first check whether the table is exists or not.

    http://stackoverflow.com/questions/10702997/raise-error-if-table-does-not-exists-in-database

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 21, 2016 11:01 AM
  • User-62323503 posted

    Would suggest to first understand the basics of Transactions and how it works.

    http://www.itdeveloperzone.com/2013/07/transaction-in-sql-server.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 22, 2016 5:22 AM