CODEWITHSUNDEEP
mysqlsqlstored-procedures
Martin AJ
Martin AJ

Reputation: 6697

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?

Upvotes: 0

Views: 387

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

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);

Upvotes: 1

Related Questions