Using Try Catch in Procedure with Multiple Update queries

  • brianconner

    Old Hand

    Points: 370

    All, I am trying to implement a Try Catch in a stored procedure that contains multiple Update routines (see below code). Is the below the correct way to do this? Any help is appreciated.

     

    BEGIN TRY

    /*This routine sets any SOAP Note where there is no matched claims that has an encounter date >= 12 months from current date to Completed- Not Paid*/
    BEGIN

    UPDATE [dbo].[PI_Worklist]
    SET [Action_Taken] = 'Completed-Not Paid',
    [COMMENTS] = 'Unmatched Claim Greater Than 1 Year Old. These are no longer eligible for Payment. Auto-Process'
    WHERE (([Action_Taken] IS NULL OR [Action_Taken]='') AND [CATEGORY] = 'UNMATCHED' AND [Payment_Amount] IS NULL AND DATEDIFF(d,[Encounter_Date],GETDATE()) >=365)

    END


    /*Identify items previously marked as Needs Further Research to see if they are now Payable*/
    BEGIN

    UPDATE T1
    SET = '9999',
    [Action_Taken] = 'Completed-Paid',
    [User_Update_TimeStamp] = GETDATE(),
    [COMMENTS] = 'Previously Needed Research AUTO-UPDATED With Completed Paid',
    [Payment_Date] = REPLACE(CONVERT(VARCHAR,GETDATE(),102),'.','-'),
    [Payment_Amount] = CASE WHEN [Submission_Type] = 'ELECTRONIC' THEN 100 ELSE 50 END

    FROM [dbo].[PI_Worklist] T1
    WHERE ([CATEGORY] = 'NEEDS FURTHER RESEARCH'
    AND YEAR([Encounter_Date]) = '2018'
    AND ISNULL([Payment_Amount],'0') = '0'
    AND ISNULL([Action_Taken],'NA') NOT IN ('Completed-Paid','Completed-Not Paid','Hold for Research')
    AND LEN([TAXID]) = '9'
    AND (LEN([Vendor_Address1]) >'0' OR LEN([Vendor_Address2]) >'0')
    AND LEN([Vendor_City]) > '0'
    AND LEN([Vendor_St]) >'0'
    AND LEN([Vendor_Zip]) >'0'
    AND LEN([PlanGrouptype]) >'0'
    AND LEN([PROV_NM]) >'0'
    )

    END



    /*Auto Update the Incentives with Completed Paid in preparation to be sent to DMS for check generation*/
    BEGIN
    UPDATE T1
    SET = '9999',
    [Action_Taken] = 'Completed-Paid',
    [User_Update_TimeStamp] = GETDATE(),
    [COMMENTS] = 'AUTO-UPDATED With Completed Paid',
    [Payment_Date] = REPLACE(CONVERT(VARCHAR,GETDATE(),102),'.','-'),
    [Payment_Amount] = CASE WHEN [Submission_Type] = 'ELECTRONIC' THEN 100 ELSE 50 END

    FROM [dbo].[PI_Worklist] T1

    WHERE [Action_Taken] IS NULL
    AND [TAXID] IS NOT NULL
    AND LEN([TAXID]) = '9'
    AND [CATEGORY] = 'MATCHED'
    AND [Vendor_Address1] IS NOT NULL

    END


    /*Override payment amount based on TIN or NPI from table PI_Dict_Provider_Payment_Override */
    BEGIN
    UPDATE T1
    SET
    [User_Update_TimeStamp] = GETDATE(),
    [COMMENTS] = 'AUTO-UPDATED With Completed Paid. ' + T2.[NOTES],
    [Payment_Amount] = T2.[AMOUNT]

    FROM [dbo].[PI_Worklist] T1
    JOIN [CRA].[dbo].[PI_Dict_Provider_Payment_Override] T2 ON T2.[ID_NUMBER] = T1.TAXID AND T2.[ID_TYPE] = 'TIN' AND T2.[STATUS] = 'ACTIVE'

    WHERE T1.[Action_Taken] = 'Completed-Paid'
    AND T1. = '9999'
    AND T1.[Payment_Date] BETWEEN T2.[EFF_DATE] AND T2.[END_DATE]
    AND T1.[Payment_Date] = REPLACE(CONVERT(VARCHAR,GETDATE(),102),'.','-')
    END





    /*******************************************Jake Brown NV Overrides, pay $100 for progress notes******************************************/
    BEGIN
    UPDATE T1
    SET
    [User_Update_TimeStamp] = GETDATE(),
    [COMMENTS] = 'AUTO-UPDATED With Completed Paid. ' + T2.[NOTES],
    [Payment_Amount] = T2.[AMOUNT]

    FROM [dbo].[PI_Worklist] T1
    JOIN [CRA].[dbo].[PI_Dict_Provider_Payment_Override] T2 ON T2.[ID_NUMBER] = T1.[PractitionerNPI]
    AND T2.[ID_TYPE] = 'NPI'
    AND T2.[STATUS] = 'ACTIVE'
    AND T2.[NOTE_TYPE] = 'Progress Note Only'
    AND T2.[NOTES] LIKE '%Jake has agreed to pay a few of the Providers in NV $100%'


    WHERE T1.[Action_Taken] = 'Completed-Paid'
    AND T1. = '9999'
    AND T1.[SubmissionType2] = 'Progress Note Only'
    AND T1.[Encounter_Date] BETWEEN T2.[EFF_DATE] AND T2.[END_DATE]
    AND T1.[Payment_Date] BETWEEN '11/09/2018' AND '03/31/2019'
    AND T1.[Payment_Date] = REPLACE(CONVERT(VARCHAR,GETDATE(),102),'.','-')
    AND T1.ST = 'NV'
    AND T1.[Source] NOT LIKE '%AVAIL%'
    END



    /************************************Mid-Level and PA Overrides**********************/
    BEGIN

    UPDATE T1
    SET T1.[TAXID] = T2.[TAXID],
    T1.[PROV_NM] = T2.[VendorName],
    T1.[Vendor_Address1] = T2.[Vendor_Address_1],
    T1.[Vendor_Address2] = T2.[Vendor_Address_2],
    T1.[Vendor_City] = T2.[Vendor_City],
    T1.[Vendor_St] = T2.[Vendor_St],
    T1.[Vendor_Zip] = T2.[Vendor_Zip],
    T1. = '9999',
    T1.[Action_Taken] = 'Completed-Paid',
    T1.[User_Update_TimeStamp] = GETDATE(),
    T1.[COMMENTS] = 'AUTO-UPDATED With Completed Paid Mid-Level Override',
    T1.[Payment_Date] = REPLACE(CONVERT(VARCHAR,GETDATE(),102),'.','-'),
    T1.[Payment_Amount] = CASE WHEN T1.[Submission_Type] = 'ELECTRONIC' THEN 100 ELSE 50 END

    FROM [dbo].[PI_Worklist] T1
    JOIN [dbo].[PI_Dict_TIN__NPI_Master_List] T2 ON T2.[NPI] = T1.[PractitionerNPI] AND T2.[Action] = 'MID LEVEL OVERRIDE' AND T2.[Status] = 'ACTIVE'
    WHERE T1.[Source] NOT LIKE '%AVAIL%'
    AND ISNULL(T1.[Action_Taken],'NA') NOT LIKE '%COMPLETE%'
    AND ISNULL(T1.[Action_Taken],'NA') NOT LIKE '%RESEARCH%'
    AND T1.CATEGORY = 'UNMATCHED'


    END


    END TRY

    /*If there is an error rollback changes and display error message*/
    BEGIN CATCH
    SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_STATE() AS ErrorState,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage;

    -- Transaction uncommittable
    IF (XACT_STATE()) = -1
    ROLLBACK TRANSACTION

    -- Transaction committable
    IF (XACT_STATE()) = 1
    COMMIT TRANSACTION

    END CATCH
  • Phil Parkin

    SSC Guru

    Points: 243531

    That's not really the way to do it.

    You can remove all of those BEGINs and ENDs (except those pertaining to TRY and CATCH).

    In your CATCH block, you should probably use RAISERROR or THROW. Not a SELECT, which has a good chance of masking the error.

    Do you want to commit the transaction even if there has been an error? If not, the COMMIT should be moved so that it is within the TRY block and the CATCH block should perform the ROLLBACK.

    I do not see a BEGIN TRAN anywhere ... presumably you have this elsewhere in your code?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Phil Parkin

    SSC Guru

    Points: 243531

    Here is a link to a code template that should push you in a better direction, regarding structure.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • brianconner

    Old Hand

    Points: 370

    Phil,

    Per your advice I have made the following revisions. Is this accurate?

     

    SET XACT_ABORT ON;


    BEGIN TRY

    BEGIN TRANSACTION;

    /*This routine sets any SOAP Note where there is no matched claims that has an encounter date >= 12 months from current date to Completed- Not Paid*/

    UPDATE [dbo].[PI_Worklist]
    SET [Action_Taken] = 'Completed-Not Paid',
    [COMMENTS] = 'Unmatched Claim Greater Than 1 Year Old. These are no longer eligible for Payment. Auto-Process'
    WHERE (([Action_Taken] IS NULL OR [Action_Taken]='') AND [CATEGORY] = 'UNMATCHED' AND [Payment_Amount] IS NULL AND DATEDIFF(d,[Encounter_Date],GETDATE()) >=365)




    /*Identify items previously marked as Needs Further Research to see if they are now Payable*/


    UPDATE T1
    SET = '9999',
    [Action_Taken] = 'Completed-Paid',
    [User_Update_TimeStamp] = GETDATE(),
    [COMMENTS] = 'Previously Needed Research AUTO-UPDATED With Completed Paid',
    [Payment_Date] = REPLACE(CONVERT(VARCHAR,GETDATE(),102),'.','-'),
    [Payment_Amount] = CASE WHEN [Submission_Type] = 'ELECTRONIC' THEN 100 ELSE 50 END

    FROM [dbo].[PI_Worklist] T1
    WHERE ([CATEGORY] = 'NEEDS FURTHER RESEARCH'
    AND YEAR([Encounter_Date]) = '2018'
    AND ISNULL([Payment_Amount],'0') = '0'
    AND ISNULL([Action_Taken],'NA') NOT IN ('Completed-Paid','Completed-Not Paid','Hold for Research')
    AND LEN([TAXID]) = '9'
    AND (LEN([Vendor_Address1]) >'0' OR LEN([Vendor_Address2]) >'0')
    AND LEN([Vendor_City]) > '0'
    AND LEN([Vendor_St]) >'0'
    AND LEN([Vendor_Zip]) >'0'
    AND LEN([PlanGrouptype]) >'0'
    AND LEN([PROV_NM]) >'0'
    )


    /*Auto Update the Incentives with Completed Paid in preparation to be sent to DMS for check generation*/

    UPDATE T1
    SET = '9999',
    [Action_Taken] = 'Completed-Paid',
    [User_Update_TimeStamp] = GETDATE(),
    [COMMENTS] = 'AUTO-UPDATED With Completed Paid',
    [Payment_Date] = REPLACE(CONVERT(VARCHAR,GETDATE(),102),'.','-'),
    [Payment_Amount] = CASE WHEN [Submission_Type] = 'ELECTRONIC' THEN 100 ELSE 50 END

    FROM [dbo].[PI_Worklist] T1

    WHERE [Action_Taken] IS NULL
    AND [TAXID] IS NOT NULL
    AND LEN([TAXID]) = '9'
    AND [CATEGORY] = 'MATCHED'
    AND [Vendor_Address1] IS NOT NULL




    /*Override payment amount based on TIN or NPI from table PI_Dict_Provider_Payment_Override */

    UPDATE T1
    SET
    [User_Update_TimeStamp] = GETDATE(),
    [COMMENTS] = 'AUTO-UPDATED With Completed Paid. ' + T2.[NOTES],
    [Payment_Amount] = T2.[AMOUNT]

    FROM [dbo].[PI_Worklist] T1
    JOIN [CRA].[dbo].[PI_Dict_Provider_Payment_Override] T2 ON T2.[ID_NUMBER] = T1.TAXID AND T2.[ID_TYPE] = 'TIN' AND T2.[STATUS] = 'ACTIVE'

    WHERE T1.[Action_Taken] = 'Completed-Paid'
    AND T1. = '9999'
    AND T1.[Payment_Date] BETWEEN T2.[EFF_DATE] AND T2.[END_DATE]
    AND T1.[Payment_Date] = REPLACE(CONVERT(VARCHAR,GETDATE(),102),'.','-')




    /*******************************************Jake Brown NV Overrides, pay $100 for progress notes******************************************/

    UPDATE T1
    SET
    [User_Update_TimeStamp] = GETDATE(),
    [COMMENTS] = 'AUTO-UPDATED With Completed Paid. ' + T2.[NOTES],
    [Payment_Amount] = T2.[AMOUNT]

    FROM [dbo].[PI_Worklist] T1
    JOIN [CRA].[dbo].[PI_Dict_Provider_Payment_Override] T2 ON T2.[ID_NUMBER] = T1.[PractitionerNPI]
    AND T2.[ID_TYPE] = 'NPI'
    AND T2.[STATUS] = 'ACTIVE'
    AND T2.[NOTE_TYPE] = 'Progress Note Only'
    AND T2.[NOTES] LIKE '%Jake has agreed to pay a few of the Providers in NV $100%'


    WHERE T1.[Action_Taken] = 'Completed-Paid'
    AND T1. = '9999'
    AND T1.[SubmissionType2] = 'Progress Note Only'
    AND T1.[Encounter_Date] BETWEEN T2.[EFF_DATE] AND T2.[END_DATE]
    AND T1.[Payment_Date] BETWEEN '11/09/2018' AND '03/31/2019'
    AND T1.[Payment_Date] = REPLACE(CONVERT(VARCHAR,GETDATE(),102),'.','-')
    AND T1.ST = 'NV'
    AND T1.[Source] NOT LIKE '%AVAIL%'




    /************************************Mid-Level and PA Overrides**********************/

    UPDATE T1
    SET T1.[TAXID] = T2.[TAXID],
    T1.[PROV_NM] = T2.[VendorName],
    T1.[Vendor_Address1] = T2.[Vendor_Address_1],
    T1.[Vendor_Address2] = T2.[Vendor_Address_2],
    T1.[Vendor_City] = T2.[Vendor_City],
    T1.[Vendor_St] = T2.[Vendor_St],
    T1.[Vendor_Zip] = T2.[Vendor_Zip],
    T1. = '9999',
    T1.[Action_Taken] = 'Completed-Paid',
    T1.[User_Update_TimeStamp] = GETDATE(),
    T1.[COMMENTS] = 'AUTO-UPDATED With Completed Paid Mid-Level Override',
    T1.[Payment_Date] = REPLACE(CONVERT(VARCHAR,GETDATE(),102),'.','-'),
    T1.[Payment_Amount] = CASE WHEN T1.[Submission_Type] = 'ELECTRONIC' THEN 100 ELSE 50 END

    FROM [dbo].[PI_Worklist] T1
    JOIN [dbo].[PI_Dict_TIN__NPI_Master_List] T2 ON T2.[NPI] = T1.[PractitionerNPI] AND T2.[Action] = 'MID LEVEL OVERRIDE' AND T2.[Status] = 'ACTIVE'
    WHERE T1.[Source] NOT LIKE '%AVAIL%'
    AND ISNULL(T1.[Action_Taken],'NA') NOT LIKE '%COMPLETE%'
    AND ISNULL(T1.[Action_Taken],'NA') NOT LIKE '%RESEARCH%'
    AND T1.CATEGORY = 'UNMATCHED'



    COMMIT TRANSACTION;

    END TRY

    /*If there is an error rollback changes and display error message*/
    BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000)
    DECLARE @ErrorSeverity INT
    DECLARE @ErrorState INT
    DECLARE @ErrorLine INT

    SELECT
    @ErrorState = ERROR_STATE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorLine = ERROR_LINE(),
    @ErrorMessage = ERROR_MESSAGE();

    -- Transaction uncommittable
    IF (XACT_STATE()) = -1
    ROLLBACK TRANSACTION
    RAISERROR (@ErrorLine, @ErrorMessage, @ErrorSeverity, @ErrorState)

    END CATCH
  • Joe Torre

    SSChampion

    Points: 10234

    You should consider writing to a logging table after the rollback, so it will persist, to track what happened and why.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply