If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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?

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:

call stored procedure

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.

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:

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

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:

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?

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.