Martin AJ Martin AJ - 3 months ago 11
SQL Question

How can I store the result of a query into a variable and use it multiple times?

I have a query (trigger) like this:

BEGIN
IF (new.edited_id IS NULL) THEN
IF (SELECT 1 FROM users WHERE id = new.author_id AND IFNULL((active & b'1000000' > 0), 0) < 1) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "you cannot ask anymore";
ELSEIF (SELECT 1 FROM users WHERE id = new.author_id AND IFNULL((active & b'10000000' > 0), 0) < 1) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "you cannot answer anymore";
END IF;
ELSE
IF (SELECT 1 FROM users WHERE id = new.author_id AND IFNULL((active & b'100000000' > 0), 0) < 1) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "you cannot edit anymore";
END IF;
END


As you see, the conditions of those inner
IF
statements are almost the same. Well how can I improve it?

I mean, how can I store the result of following query into a variable:

SELECT active FROM users WHERE id = new.author_id;


And then use it into those conditions like these:

IF (IFNULL((@variable & b'1000000' > 0), 0) < 1) THEN
IF (IFNULL((@variable & b'10000000' > 0), 0) < 1) THEN
IF (IFNULL((@variable & b'100000000' > 0), 0) < 1) THEN


How can I handle that?

Answer

Declare a local variable and set it with the output from select statement.

DECLARE v_active bit(10);
SET v_active := (SELECT active FROM users WHERE id = new.author_id);
Comments