I have a stored procedure that is returning a set of data from a table and then is populating a summary table with that data. if sussful I am deleting all the data from the first table. if there is an error I do not want to delete the data and notify me if it fails. I am using a try-catch on the stored proc so if the insert fails it falls to the catch. in the SQL agent job I know, I can set a notification on failure. Will the SQL agent job detect my catch as a failure?
November 18, 2021 at 6:49 pm
It depends. Can you show us what is in your CATCH block?
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
November 18, 2021 at 6:58 pm
Create procedure [dbo].[TO_POPULATE_ISD_MonthEndDetail] AS
begin Try
insert into [dbo].[U5TO_ISD_MONTHENDDETAIL] ([MOD_DOCID], [MOD_STORENO], [MOD_STOREORG], [MOD_AIMDATE], [MOD_PARTCOUNT],
[MOD_PRT], [MOD_QTY], [MOD_AVP], [MOD_BATCHID], [MOD_CREATEDATE], [MOD_MONTHYEAR])
SELECT
[ISD_DOCID], [ISD_STORENO], [ISD_STOREORG], [ISD_AIMDATE], [ISD_PARTCOUNT], [ISD_PRT], [ISD_QTY], [ISD_AVP], [ISD_BATCHID], [ISD_CREATEDATE],
RIGHT('00' + CAST(DATEPART(MM, GETDATE()) AS VARCHAR(2)) , 2) + '/'+ CAST(DATEPART(YYYY, GETDATE()) AS VARCHAR(4)) [MONTHYEAR]
FROM (
SELECT *, DENSE_RANK() OVER(PARTITION BY [ISD_STOREORG] ORDER BY ISD_CREATEDATE DESC) AS rank_num
FROM [U5TO_InventoryStoreDetails]
) AS derived1
WHERE rank_num = 1
end try
begin catch
SELECT ERROR_MESSAGE() AS ErrorMessage;
END catch
That won't do it. When processing is transferred to your CATCH block, your SELECT merely returns the error message and the error is swallowed. I suggest the following slight modification:
begin catch
SELECT ERROR_MESSAGE() AS ErrorMessage;
THROW;
END catch
This will ensure that the calling application (SQL Agent) knows about the error.
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
November 18, 2021 at 7:29 pm
Thank you, i forgot about Throw (just like in C#) I am a C# developer and so so on SQL
November 19, 2021 at 5:06 pm
I would suggest putting all of the logic in the one procedure - instead of separate steps in an agent job. Start a transaction, perform your insert and delete in a try/catch, commit after the delete. If it fails, perform a rollback in the catch and throw an error so the agent job fails and notification is sent on failure.
Another option - slightly different, would be to perform the delete first using the OUTPUT parameter into a temp table (or table variable) and then insert into your monthly table from the temp table. You would still wrap this all in a transaction and rollback on failure...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply