Sql-server – Exit a query in a case statement

sql servert-sql

I'm trying to setup a query, so that it will compare two dates from two different tables, and if they are equal, then the query exits. If they are not equal, then the query will continue and insert some stuff. I can't figure out how to get it to do what I want however.

SELECT TOP(1) @dateA=a.someDate
FROM a
ORDER BY DESC;
SELECT TOP(1) @dateB=b.someDate
FROM b
ORDER BY DESC;

CASE WHEN @dateA=@dateB THEN raiseerror('dates equal',20,-1) with log;

Insert statements;

Any help would be super appreciated.

Best Answer

CASE is an expression (not a statement) and cannot be used for control-of-flow like that - not to call commands, not to return more than one column/value, not to be used as a command on its own.

It seems to me you can just use IF to raise the error when the dates are equal, otherwise run the inserts.

IF @dateA = @dateB 
BEGIN
  raiseerror('dates equal',20,-1) with log;
END
ELSE -- maybe you don't need a batch-aborting, logging error level
BEGIN
  INSERT ...
END

You could also do it the other way. Run the inserts only if the dates are not equal, otherwise raise the error:

IF @dateA <> @dateB
BEGIN
  INSERT ...
END
ELSE
BEGIN
  raiserror ...
END

If you thought to use the error only for the purpose of getting out of running the inserts, then you can just remove everything from ELSE down, since the only way the inserts will run is when @dateA and @dateB are not equal:

IF @dateA <> @dateB
BEGIN
  INSERT ...
END

I have scaled way back on being pedantic about things like rows (vs. "records"), and columns (vs. "fields"), but the whole expression vs. statement thing is a very important distinction, for exactly this reason. See "Dirty Secrets of the CASE Expression."

Related Question