call stored procedure

melgra70 · #1 (**permalink**) 03-29-11, 06:46

call stored procedure
I've written a stored procedure as follows: I need to be able to enter a judge_id to retrieve judge_name and judge_suburb. The fields in my table are judge_id, judge_name and judge_suburb. The procedure appears to be error free.
I now need to write a statement to call the procedure so I can test it. I'm having difficulty finding guidance for this syntax for MySQL. Can anyone point me in the right direction, please?

delimiter //

drop procedure if exists judge_sp //
create procedure judge_sp
(in judge_id int,
out judge_name varchar(20),
judge_suburb varchar(30))
begin
select judge_name into judge_name
from judge
where id = judge_id;
select judge_suburb into judge_suburb
from judge
where id = judge_id;

end;
//

delimiter ;

it-iss.com · #2 (**permalink**) 03-29-11, 08:20

To call a stored procedure in MySQL you have to use "CALL judge_sp(input, @x, input);"

I would group all input fields together. Rather than having input, output and then another input field as parameters to the stored procedure. The @x is a session variable. To view the value of that issue "SELECT @x;"

I have just looked at your stored procedure again and there are improvements that could be made:

Code:

CREATE PROCEDURE judge_sp (IN judge_id      INT,
                           OUT judge_name   VARCHAR(20),
                           OUT judge_suburb VARCHAR(30))
BEGIN
  SELECT judge_name,
         judge_suburb
  INTO   judge_name, judge_suburb
  FROM   judge
  WHERE  id = judge_id;
END;

Also consider what should happen if someone requests a judge_id that does not exist in the database table? There are handlers that you can use for this. A handler is defined as follows:

Code:

CREATE PROCEDURE judge_sp (IN judge_id      INT,
                           OUT judge_name   VARCHAR(20),
                           OUT judge_suburb VARCHAR(30))
BEGIN
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET judge_name = 'Not found';
  SELECT judge_name,
         judge_suburb
  INTO   judge_name, judge_suburb
  FROM   judge
  WHERE  id = judge_id;
END;

melgra70 · #3 (**permalink**) 03-29-11, 17:26

Thanks very much, Ronan.

melgra70 · #4 (**permalink**) 03-30-11, 00:02

With the call statement, Ronan, I don't understand the three parameters this requires.
I ran the select @x statement and it returned a NULL.
Sorry to be thick! I thought the only input that would be required would be the judge id.

melgra70 · #5 (**permalink**) 03-30-11, 01:17

I've been reading up on sessions and found it helpful but it didn't give me enough to apply to the situation I'm working with.

it-iss.com · #6 (**permalink**) 03-30-11, 03:06

For your information @name are MySQL user variables. You can create as many as you want. Stored procedures can have none, one or more than one parameter passed in or out of the stored procedure. The type of parameter IN or OUT is defined before the parameter name and data type in the CREATE PROCEDURE. A parameter without a type by default is an IN parameter.

In the case above, there is one IN parameter and two out parameters. To make this work what you would need to do is:

Code:

CALL judge_sp(1, @name, @suburb);

The 1 is some judge identifier value.

The user variable @name will contain the judge name and @suburb the suburb. To view these in MySQL simply execute:

Code:

SELECT @name, @suburb;

melgra70 · #7 (**permalink**) 03-30-11, 05:18

Thanks for your excellent explanations, Ronan. I believe I'm learning heaps and I understand the CALL statement and the parameters now.
However, I'm getting an error message when I run the CALL statement:

ERROR 1054 (42S22): Unknown column 'id' in 'where clause'
When I previously ran the CREATE stored procedure, the Query was OK (I'm using the one with the handler added in).
I tried changing the where statement to

WHERE judge_id = judge_id;

but it still returned the same error message.

it-iss.com · #8 (**permalink**) 03-30-11, 10:56

Hi,

actually it is not wise to have the parameters named the same as the individual fields that they access. This can lead to odd behaviours. I suggest renaming the parameters to include a leading p to indicate that these are actually parameters:

Code:

CREATE PROCEDURE pjudge_sp (IN judge_id      INT,
                           OUT pjudge_name   VARCHAR(20),
                           OUT pjudge_suburb VARCHAR(30))
BEGIN
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET pjudge_name = 'Not found';
  SELECT judge_name,
         judge_suburb
  INTO   pjudge_name, pjudge_suburb
  FROM   judge
  WHERE  id = pjudge_id;
END;

Check your original code and there you specified id when searching the judge table. Drop the original stored procedure and reload this stored procedure.

melgra70 · #9 (**permalink**) 04-01-11, 04:56

Thanks, Ronan: that's very clear direction with the parameters.
I edited my code to reflect the parameter changes and edited the call statement to:

call pjudge_sp(4, @name, @suburb);

This still evoked the same where clause error message.

So I tried changing my 'where' clause to where judge_id = pjudge_id.
This changed the error message to "unknown column 'pjudge_id' in 'where clause'.
I can't see what else to try in this clause: my call statement is correct, isn't it?

it-iss.com · #10 (**permalink**) 04-01-11, 06:41

Can you give the table definition of judge?

melgra70 · #11 (**permalink**) 04-01-11, 07:44

judge_id int not null auto_increment primary key,
judge_name varchar(20) not null,
judge_address varchar(30) not null

it-iss.com · #12 (**permalink**) 04-01-11, 10:12

Hi, I think I made a typo in the previous posting with regards the stored procedure. It should have been:

Code:

CREATE PROCEDURE judge_sp (IN pjudge_id      INT,
                           OUT pjudge_name   VARCHAR(20),
                           OUT pjudge_suburb VARCHAR(30))
BEGIN
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET pjudge_name = 'Not found';
  SELECT judge_name,
         judge_suburb
  INTO   pjudge_name, pjudge_suburb
  FROM   judge
  WHERE  judge_id = pjudge_id;
END;

melgra70 · #13 (**permalink**) 04-01-11, 22:29

Thanks, Ronan. That's so cool! Thanks for being an excellent tutor.
I have a remaining thread still being looked at by dav1mo but I'm waiting on his further guidance (calculate query and subquery).
The other one is the triggers question which I'll investigate further.
Thanks again for your help.
Mel.

niranjana · #14 (**permalink**) 04-06-11, 11:11

Hi,

Is there any open source tool to run the store procedure

thanks

it-iss.com · #15 (**permalink**) 04-06-11, 11:35

mysql command line utility allows stored procedures to be called, this can be called via PHP, I am pretty sure also phpMyAdmin will allow stored procedures to be called as well as MySQL Workbench.

Internet Services

Web Development

Digital Marketing

Consumer Tech