How to cancel a query if a condition is not met?

• lindsayscott23

  SSCrazy

  Points: 2339

  More actions

  April 23, 2015 at 4:59 am

  #296346

  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

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  April 23, 2015 at 5:05 am

  #1792973

  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

• lindsayscott23

  SSCrazy

  Points: 2339

  More actions

  April 23, 2015 at 5:19 am

  #1792974

  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