1

I need to know if the T-SQL below is correct to roll back if something bad happens.

I first create a temp table

IF OBJECT_ID(N'tempdb..#LocalSummary') IS NOT NULL  
    DROP TABLE #LocalSummary   

CREATE TABLE  #LocalSummary (

then I populate the table

INSERT INTO #LocalSummary
   SELECT *....

Then I start my try catch

BEGIN TRY
 BEGIN TRANSACTION 

    DELETE FROM LocalSummaryRealTable

    INSERT INTO LocalSummaryRealTable
        SELECT S.*
        FROM #LocalSummary AS S 

    COMMIT
END TRY
BEGIN CATCH
       IF @@TRANCOUNT > 0
              ROLLBACK TRANSACTION 
    ...

I noticed when I did the insert into the LocalSummaryRealTable the columns didn't line up and I got an error. I expected things to roll back but in SSMS query window it gave me a message that the trans wasn't committed and asked me if I wanted to commit it. Why would this ask me when it should of been rolled back?

2

1 Answer 1

3

You want to do the BEGIN TRANSACTION prior to the BEGIN TRY:

BEGIN TRANSACTION 
BEGIN TRY
    DELETE FROM dbo.LocalSummeryRealTable;
    INSERT INTO dbo.LocalSummeryRealTable
    SELECT S.*
    FROM  #LocalSummery' AS S;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
       IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; 
END CATCH

Also, please schema-qualify references to database objects such as tables, views, stored procedures etc. Also, you should get in the habit of adding semi-colons to the end of each statement.

SET XACT_ABORT ON; is not necessary if you are explicitly rolling back the transaction inside the CATCH block.

2
  • thanks ,i'll try it out. do i need SET XACT_ABORT ON ? not real sure if this is another way to roll back or I need to do both Oct 14, 2016 at 23:36
  • 1
    I added that detail to my answer
    – Hannah Vernon
    Oct 14, 2016 at 23:41

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .