Using Stored Procedures

Estimated reading: 1 minute 207 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

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

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\”, \”\”]
sql :	{ call SHOW_SUPPLIERS()}

Using Stored Procedures

Using Stored Procedures

CONTENTS