Using Stored Procedures

For procedure that returns a single value

DELIMITER //
Create Procedure subjects (IN S_Subject VARCHAR(25), OUT total INT)
BEGIN
SELECT count(subject)
INTO total
FROM student_info
WHERE subject = S_subject;
END //
DELIMITER ;

Procedure

The table named “student_info”

param1 :

[“1”, “STRING”, “IN”, “History”]

param2 :

[“2”, “INTEGER”, “OUT”, “@total”]

• Once the parameters are defined correctly, all we have to do is write our query. The correct usage for this query is as follows:

Stored procedure :

{call subjects(?,?)}

For procedure that returns Resultset

DELIMITER //
create procedure SHOW_SUPPLIERS()
    begin
        select name, address
        from student_info;
    end//
DELIMITER ;

Procedure

The table named “student_info”

Procedure call :

call SHOW_SUPPLIERS();

• When the above query is made, the result that will return is as follows.

• The parameters required while designing the process in the modeler are as follows;