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?
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.
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy