CODEWITHSUNDEEP
c#sql.nett-sqlado.net
Denis Stephanov
Denis Stephanov

Reputation: 5241

Correct way to handle ERROR in TSQL transaction

I have transaction in with this structure:

CREATE PROCEDURE XXX @param bit
AS
BEGIN TRAN

IF (@param = 1) BEGIN

   -- DO SOME STUFF LIKE INSERT, UPDATE AND SO ON...
   RETURN 1 -- RETURN 1 FOR SUCCESS
END
ELSE BEGIN
   -- CONDITION IS NOT SUCCESSFUL SO NOTHING HAPPENED AND I NEED HANDLE
   -- IN C#
   RETURN -1
END


BEGIN TRY
   COMMIT TRAN
END TRY
BEGIN CATCH
  ROLLBACK TRAN
  RETURN -1
END CATCH

I've added some return clausule but I don't know if is it good solution, and handle it in c# code? In c# I need handle only 2 states:

Is that procedure body ok for that?

Thanks for answers

EDIT For better understand what I need, I've created state diagram: enter image description here

Upvotes: 1

Views: 96

Answers (1)

Matej Hlavaj
Matej Hlavaj

Reputation: 1038

For me is better to know where was problem during the procedure. RAISEERROR should be best solution. But ofc it can be considered as point of view.

That kind of solution can help me also handle Exception in .NET application.

In this case procedure should seems like

CREATE PROCEDURE XXX @param bit
AS
BEGIN


DECLARE @ErrorMessage NVARCHAR(4000) 
DECLARE @ErrorSeverity INT 
DECLARE @ErrorState INT 


BEGIN TRY

BEGIN TRAN TR1   


IF (@param = 1) 
BEGIN

   -- DO SOME STUFF LIKE INSERT, UPDATE AND SO ON...
   -- RETURN 1 FOR SUCCESS
END
ELSE 
BEGIN
   -- CONDITION IS NOT SUCCESSFUL SO NOTHING HAPPENED AND I NEED HANDLE
   -- IN C#
   RAISERROR ('Incorrect input param error.', -- Message text.  
           16, -- Severity.  
           1 -- State.  
           ); 
END

    RETURN 1

COMMIT TRAN TR1
END TRY

BEGIN CATCH

    IF @@TRANCOUNT > 0
    ROLLBACK TRAN TR1

    SET @ErrorMessage = ERROR_MESSAGE()  
    SET @ErrorSeverity = ERROR_SEVERITY()  
    SET @ErrorState = ERROR_STATE() 


    RAISERROR (@ErrorMessage,-- Message text.  
               @ErrorSeverity,-- Severity.  
               @ErrorState-- State.  
               );  
-- this catch also possible errors if @param = 1

END CATCH
END

Upvotes: 1

Related Questions