Assertion in MySQL

Question

I have a SQL script to run against a large database. I'd like to put a couple of simple queries at the start, just as a sanity check.

Is there any way to write an assertion in MySQL? Or any kind of "select ..., and if it doesn't match this value, then abort the entire script"?

Answer 1

Some crazy code. Main point is: SET could raise error for mysql variables.

For example.

 SET @value = 0;
 SET SESSION sql_mode = if(@value, @@SESSION.sql_mode, 'something wrong uphere'); 

Would output ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'something wrong uphere' and execution would be stopped.

This approach is not semantic but it works.

Answer 2

You could put the entire script in an if statement, depending on what kind of value you need to check, here's an example:

DECLARE @value int
SET @value = (SELECT COUNT(*) FROM dbo)

IF @value >0
BEGIN
 --Do Stuff Here
END

Answer 3

You can also do this via a stored procedure / function, as in the example below:

CREATE FUNCTION `RunProcess`() RETURNS INT(11)
runProcess:BEGIN

DECLARE check_value INT;
DECLARE error_code INT;

SELECT COUNT(*) FROM dbo INTO check_value;

IF check_value = 0 THEN set error_code = 666;
    LEAVE runProcess;
    RETURN error_code;
END IF;

...
...
END;