Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Unanswered: call stored procedure

    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 ;

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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;
    Last edited by it-iss.com; 03-29-11 at 09:25.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    call stored procedure

    Thanks very much, Ronan.

  4. #4
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    call stored procedure - parameters

    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.

  5. #5
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58
    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.

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  7. #7
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58
    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.

  8. #8
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  9. #9
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58
    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?

  10. #10
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Can you give the table definition of judge?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  11. #11
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58
    judge_id int not null auto_increment primary key,
    judge_name varchar(20) not null,
    judge_address varchar(30) not null

  12. #12
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  13. #13
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58
    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.

  14. #14
    Join Date
    Apr 2011
    Posts
    3

    tool to run store procedure

    Hi,

    Is there any open source tool to run the store procedure

    thanks

  15. #15
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •