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?

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.

|improve this answer|||||
  • 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 – Andre DeMattia Oct 14 '16 at 23:36
  • 1
    I added that detail to my answer – Max Vernon Oct 14 '16 at 23:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.