CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Trefle
Trefle

Reputation: 19

Stored procedure - do not execute if any part of the procedure fails

SQL Server 2008

I have a stored procedure that selects data from a source, inserts it into a staging table, deletes some rows from the fact table and then inserts more rows from the staging table into the mentioned fact table.

I would like to not execute the procedure if ANY part of the code fails. So for example if deletion of the rows fail because of some reason I would like to have the stage table also unchanged.

Is it possible? To 'check' the code and insert tables before running the code?

Edit: thank you! I implemented one of the solutions you suggested. The thing is I have a procedure nested in another procedure (try catch nested in another try catch) - I want to catch the errors and insert them to the errorlog table I created. The error catching worked as expected before but after the changes errors are not inserted to the table anymore and I'm getting the error: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

The query I'm running is the MAIN procedure (below)

the structure of my first query is: 

[procedure instertdata]
BEGIN TRANSACTION 

BEGIN TRY

  INSERT INTO Your_Table (Col1, Col2)
  VALUES ....

  UPDATE Your_Table 
  SET [Col1] = .......
  WHERE ........

  COMMIT TRANSACTION 

END TRY

BEGIN CATCH
INSERT INTO MyErrorLogTable
  SELECT  AffectedTable = 'mytable',
         ERROR_NUMBER()    AS ErrorNumber,
         ERROR_SEVERITY()  AS ErrorSeverity,
         ERROR_STATE()     AS ErrorState,
         ERROR_PROCEDURE() AS ErrorProcedure,
         ERROR_LINE()      AS ErrorLine,
         ERROR_MESSAGE()   AS ErrorMessage,
         GETDATE()      AS TimeAndDate;

  IF @@trancount > 0 ROLLBACK TRANSACTION

END CATCH

and the code of the main procedure is:

[procedure main]

BEGIN TRY

EXEC [procedure instertdata]

END TRY
BEGIN CATCH

 INSERT INTO MyErrorLogTable
   SELECT  ProcedureName= 'mytable',
         ERROR_NUMBER()    AS ErrorNumber,
         ERROR_SEVERITY()  AS ErrorSeverity,
         ERROR_STATE()     AS ErrorState,
         ERROR_PROCEDURE() AS ErrorProcedure,
         ERROR_LINE()      AS ErrorLine,
         ERROR_MESSAGE()   AS ErrorMessage,
         GETDATE()      AS TimeAndDate;
END CATCH

What I'm doing wrong? How to handle it?

EDIT: I mentioned before I have a procedure nested in another one. What I did I moved the transaction rollback to the outer one. The MAIN procedure doesn't have transaction rollback.. procedure is nested So it looks like follows:

CREATE PROCEDURE [dbo].[myload] 
AS
BEGIN

 set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction [myload];

EXEC MAIN_procedure

lbexit:
        if @trancount = 0   
            commit;

END TRY
BEGIN CATCH
 declare @error int, @severity varchar(4000), @estate varchar(55), @procedure varchar (55), @eline varchar (255), @emessage varchar (4000), @edate date;
  SELECT  @error=ERROR_NUMBER(),

         @severity=ERROR_SEVERITY(),

         @estate=ERROR_STATE() ,

         @procedure=ERROR_PROCEDURE(),

         @eline=ERROR_LINE(),

         @emessage= ERROR_MESSAGE() ,

         @edate=GETDATE();


  if XACT_STATE() = -1
            rollback;
        if XACT_STATE() = 1 and @trancount = 0
            rollback
        if XACT_STATE() = 1 and @trancount > 0
            rollback transaction [mytransaction];

 INSERT INTO myErrorTable
 SELECT  ProcedureName= 'myload',
@error,@severity, @estate, @procedure, @eline, @emessage,  @edate;


END CATCH
END

Seems to work so far so thank you all for your help!

Upvotes: 0

Views: 1811

Answers (3)

NK0709
NK0709

Reputation: 66

You can use a try/catch statement and raiserror to return the control back to main procedure in the catch rollback the transaction.

But I may suggest instead of playing with the fact table, create a second staging table like 'staging_table_2' and manipulate the data in this staging, you can refer to fact tables in joins and then use a simple merge to populate the fact table.

This way you will always be sure data in your fact is clean and failures is not impacting any data inconsistency.

Upvotes: 0

Venkataraman R
Venkataraman R

Reputation: 12969

You have to use Savepoint feature to save part of the transaction, i.e., saving results to staging table. I have utilized Remus Rusunu nested transaction approach with save points. You can read the same here


declare @trancount int;
    set @trancount = @@trancount;

BeginTry

if @trancount = 0
   BEGIN TRANSACTION

INSERT INTO StagingTable... 

SAVE TRANSACTION StagingTableInsertion


DELETE FROM FactTable WHERE <CONDITION>...

INSERT INTO FactTable...
SELECT * FROM StagingTable

IF @tranCount = 0
    COMMIT TRANSACTION

EndTry
BeginCatch

declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction StagingTableInsertion;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;

EndCatch

Read more on SavePoints on mssqltips

Upvotes: 1

DineshDB
DineshDB

Reputation: 6193

Transaction block will ROLLBACK all the transactions, if any one of the transaction fails inside the transaction block.

Don't use TRUNCATE inside the transaction block, because truncate statements never rolled back.

BEGIN TRANSACTION [Tran1]

BEGIN TRY

  INSERT INTO Your_Table (Col1, Col2)
  VALUES ....

  UPDATE Your_Table 
  SET [Col1] = .......
  WHERE ........

  COMMIT TRANSACTION [Tran1]

END TRY

BEGIN CATCH

  ROLLBACK TRANSACTION [Tran1]

END CATCH

Upvotes: 2

Related Questions