CODEWITHSUNDEEP
mysql
You Know Nothing Jon Snow
You Know Nothing Jon Snow

Reputation: 833

Assign procedure to a variable

I want to select any random value i want using this procedure

delimiter $$
CREATE PROCEDURE randomdigit(troy INT)
     BEGIN
          select troy; 
     END$$
delimiter ;

To use it,i am calling call randomdigit(n);

However,when i try using assigning the procedure to a variable in a trigger i get this error

/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call randomdigit(1); SET the_class_id = (select examination_class_id from e' at line 11 */

This is my trigger

DELIMITER //
CREATE TRIGGER lestrigger
    AFTER INSERT ON examinations
   FOR EACH ROW
  BEGIN
    DECLARE the_last_inserted_id INT ;
    DECLARE the_class_id INT;
    DECLARE the_year_id INT;
    DECLARE lesrandom INT;

    SET the_last_inserted_id = LAST_INSERT_ID();
    SET lesrandom = call randomdigit(1);
    SET the_class_id = (select examination_class_id from examinations where examination_id = 1);
    SET the_year_id = (select examination_class_id from examinations where examination_id = 1);

insert into examination_data (ed_cs_id,ed_examination_id) VALUES (( select cs_id from class_students where cs_class_id = 1 AND cs_year_id = 1 ),lesrandom);

END //
DELIMITER ;

Is it correct to assign a procedure to a variable this way?.

Upvotes: 0

Views: 68

Answers (2)

fancyPants
fancyPants

Reputation: 51878

Akhil's answer is a possible solution. In case you need a stored procedure, you have to work with OUT parameters.

delimiter $$
CREATE PROCEDURE randomdigit(IN troy INT, OUT result INT)
     BEGIN
          set result = troy; 
     END$$
delimiter ;

and call it like this:

DELIMITER //
CREATE TRIGGER lestrigger
    AFTER INSERT ON examinations
   FOR EACH ROW
  BEGIN
    DECLARE the_last_inserted_id INT ;
    DECLARE the_class_id INT;
    DECLARE the_year_id INT;
    DECLARE lesrandom INT;

    SET the_last_inserted_id = LAST_INSERT_ID();
    call randomdigit(1, lesrandom);
    SET the_class_id = (select examination_class_id from examinations where examination_id = 1);
    SET the_year_id = (select examination_class_id from examinations where examination_id = 1);

insert into examination_data (ed_cs_id,ed_examination_id) VALUES (( select cs_id from class_students where cs_class_id = 1 AND cs_year_id = 1 ),lesrandom);

END //
DELIMITER ;

Upvotes: 3

Akhil
Akhil

Reputation: 2602

Change it as a function 

delimiter $$
CREATE function randomdigit(troy INT) returns int
     BEGIN
          return troy; 
     END$$
delimiter ;

And change your trigger as follows

DELIMITER //
CREATE TRIGGER lestrigger
    AFTER INSERT ON examinations
   FOR EACH ROW
  BEGIN
    DECLARE the_last_inserted_id INT ;
    DECLARE the_class_id INT;
    DECLARE the_year_id INT;
    DECLARE lesrandom INT;

    SET the_last_inserted_id = LAST_INSERT_ID();
    SET lesrandom = randomdigit(1);
    SET the_class_id = (select examination_class_id from examinations where examination_id = 1);
    SET the_year_id = (select examination_class_id from examinations where examination_id = 1);

insert into examination_data (ed_cs_id,ed_examination_id) VALUES (( select cs_id from class_students where cs_class_id = 1 AND cs_year_id = 1 ),lesrandom);

END //
DELIMITER ;

Hope this is fine

Upvotes: 2

Related Questions