April 23, 2015 at 4:59 am
Hi all,
Is it possible to stop a query from running if a condition is not met?
I have a stored procedure that returns some email addresses. A condition is that a load of new data should have happened in the current month.
If a load of new data hasn't happened I want the stored procedure to stop and return an error message instead of returning the email addresses.
I have a variable that finds the maximum data load date and I thought I could use an IF... ELSE... to check if new data had been loaded. Like this:
DECLARE @MaxLoadDate as date = (select max(load_date)
from #table)
IF @MaxLoadDate<Dateadd(month, Datediff(month, 0, Getdate()), 0) --First Day of Current Month
<Something here to kill the query>
ELSE
SELECT email_address
FROM... etc
I've tried a couple of things like PRINT 'Error', and SET NO EXEC ON, but the query seems to happily carry on past the IF condition and return the email addresses. I know that data hasn't been loaded this month, so it should fail.
Hope you can help. Any ideas hugely appreciated.
Cheers
Lins
April 23, 2015 at 5:05 am
This is not about canceling a query, it's about control flow. Try out the TRY...CATCH construct and use THROW to change how your procedure flows:
BEGIN TRY
DECLARE @MaxLoadDate AS DATE = (SELECT MAX(load_date) FROM #table);
IF @MaxLoadDate<DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) --First Day of Current Month
BEGIN
--<SOMETHING HERE TO KILL THE QUERY>
THROW 50000, 'CONDITION NOT MET', 1;
END
ELSE
BEGIN
SELECT email_address
FROM etc;
END
RETURN;
END TRY
BEGIN CATCH
THROW;
END CATCH
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 23, 2015 at 5:19 am
Hi Orlando,
Thanks so much. That works perfectly!
I had't thought about it in those terms at all, and it makes sense to.
Cheers loads for such a speedy solution.
Lins
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply