Man illustrationFlower illustration

Using Stored Procedures

Estimated reading: 1 minute 107 views
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”
  • Let’s say we’re trying to find out how many people are there in total whose “subject” is History.
  • The first of our parameters will be input(IN), its value will be “History” and its data type will be “STRING”.

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

  • Other parameter is our output (OUT) parameter, whose data type is “INTEGER”, with the “@total” value used to find out how many “History” values are in total.

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(?,?)}

  • When we run the above query, it will return “2”.
DELIMITER //
create procedure SHOW_SUPPLIERS()
    begin
        select name, address
        from student_info;
    end//
DELIMITER ;

Procedure

The table named “student_info”
  • This procedure gets the “name” and “address” information in the student_info table.

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;
param1 : [“1”, “RESULTSET”, “OUT”, “”]
query sql : { call SHOW_SUPPLIERS()}
Share this Doc

Using Stored Procedures

Or copy link

CONTENTS