August 13, 2019 at 3:49 pm
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
August 13, 2019 at 4:02 pm
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 13, 2019 at 4:13 pm
Here is a link to a code template that should push you in a better direction, regarding structure.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 13, 2019 at 4:43 pm
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
August 15, 2019 at 4:22 pm
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply