Sample Header Ad - 728x90

WHILE EXISTS Error Handling/ Raise Error

0 votes
2 answers
572 views
I am adding a step in my SQL job where it does a WHILE EXISTS check for me before executing the last step. What it does is it will wait for 15minutes to see if the table I am checking has been populated with datetime data, in order to have the correct datetime data it should be greater than midnight. If it is greater than midnight I will go ahead and move to the last step. But I was stuck to add an error handling mechanism to close out the step with error after trying for a couple of time for 15minutes... Here is the query below:
WHILE EXISTS ( SELECT * FROM ETLTimeCheck WHERE EXEC_END_TIME is NULL OR EXEC_END_TIME > DATEADD(D,0,DATEDIFF(D,0,GETDATE())))
	BEGIN
			WAITFOR DELAY '00:15:00'
	END
So I started with this but got stuck figuring out the error handling/ how to raise an error,
WHILE EXISTS ( SELECT * FROM ETLTimeCheck WHERE EXEC_END_TIME is NULL OR EXEC_END_TIME > DATEADD(D,0,DATEDIFF(D,0,GETDATE())))
	BEGIN
		BEGIN TRY
			WAITFOR DELAY '00:15:00'
		END TRY
	END

BEGIN CATCH 
-- The Error Message
END CATCH
Asked by WhoIsNotActive (13 rep)
Oct 31, 2022, 05:30 PM
Last activity: Oct 31, 2022, 07:55 PM