CODEWITHSUNDEEP
sqlsql-servert-sql
Upstart
Upstart

Reputation: 196

Multiple Error Handling in Stored Procedure

I was wondering if anyone could point me in the right direction. This is my first attempt at any type of error handling in SQL and I am afraid either I am missing something completely or misunderstand the concepts of error handling.

I have a procedure, that essentially takes in 3 values, then attempts to see if one of the values exists. If it does, then it performs an update statement.

Essentially, I would like to do the following: - Return '1' if there are no error encountered - Return an error if the Select statement fails to find the record - Return an error if the Update statement fails to update any records

Below is my procedure. Currently, whenever I run it, it is returning a '1', which indicates there is no errors, which isn't how I expected it to work as I am passing in bogus values and trying to break it. Can someone help point out what I am doing wrong or if this is even possible?

alter PROCEDURE [dbo].prc_update_SPRO_refill_status                             
@result_code char(2),                             
@result_string char(10),                                                      
@rx_id char(20)        


AS                            
BEGIN                            


SET NOCOUNT ON; 
BEGIN TRANSACTION                                
BEGIN TRY   

DECLARE @prescription_orders_id varchar(20)
DECLARE @ErrorVar INT;
DECLARE @RowCountVar INT;

IF @result_code = 0
BEGIN
SELECT @prescription_orders_id = prescription_orders_id
FROM prescription_orders
WHERE rx_external_id = @rx_id
       -- Return error if record not found

UPDATE prescription_orders_fills
SET fill_status_code = 'R'
WHERE prescription_orders_id = @prescription_orders_id
    AND fill_status_code = 'P'
    AND fill_number > 1 
      -- return error if record not found
      -- return error if more then 1 record update


END                                               
END TRY
BEGIN CATCH
SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
BEGIN
COMMIT TRANSACTION
 SELECT 1 as ERROR_NUMBER   
END                                                       
END

Upvotes: 3

Views: 2325

Answers (2)

aked
aked

Reputation: 5815

My 2 cents 

alter PROCEDURE [dbo].prc_update_SPRO_refill_status                             
@result_code char(2),                             
@result_string char(10),                                                      
@rx_id char(20)        


AS                            
BEGIN                            


SET NOCOUNT ON;

SET XACT_ABORT ON; // 1 , it's good to implement this before try

BEGIN TRY   
BEGIN TRANSACTION  ; **// 2 start transaction after try**

DECLARE @prescription_orders_id varchar(20)
DECLARE @ErrorVar INT;
DECLARE @RowCountVar INT;

IF @result_code = 0
    BEGIN
        ...
        ...
        ...
        ...  
    END                          

END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

   IF @@TRANCOUNT > 0
    BEGIN
        COMMIT TRANSACTION;
        SELECT 1 as ERROR_NUMBER  ;
    END 

END

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

I think the problem here is quite simple: you're expecting the TRY to fail and bump you into the CATCH block when no exception has been raised. Think about this: does the following query raise an error just because no row was updated?

CREATE TABLE #foo(a INT);

UPDATE #foo SET a = 1 WHERE a = 2;

There is no error message here, so no reason for CATCH to be triggered. If you want to test your error handling, put a real error condition in there. For example:

BEGIN TRY
  IF @force_error = 1
  BEGIN
    SELECT 1/0;
  END
END TRY
BEGIN CATCH
  -- now you should get here...
END CATCH

Also as I mentioned in a comment - use RETURN to send error number / status code back to the caller. There is no reason to invoke all the scaffolding required for a recordset to return a single, scalar value.

Upvotes: 2

Related Questions