Creating a routine with an input.

[wvoke9]

Hi,

I am currently creating 'Routines' to speed up the analysis of the quiz data my database stores. One of the queries I have been using which I would like to turn into a Routine looks like this.

SELECT Candidate, QuizName, PercentageScore, PassFail
FROM Results
WHERE Candidate ='Joe Bloggs'

This works perfectly but in order to turn this into a 'Routine' I would have to create a 'Routine' for every single staff member using the quiz. Is there a way where I can have the 'Joe Bloggs' as a input. So I can call the rest of the code but input a different name.

E.g.

CALL RoutineName 'Brian Smith' 

To give Brians results.

CALL RoutineName 'Sarah Smith'

To give Sarahs results.

Is this possible?

Many thanks,

Will Voke

[cereal]

Yes you can, as sample:

DELIMITER //

CREATE PROCEDURE mytest(IN name VARCHAR(200))
    BEGIN
    select * from results where candidate = name;
    END//

DELIMITER ;

Then you just need to pass the parameter name:

call mytest('Sarah Smith');

Reference: http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html

[wvoke9]

Thank you for your reply, have you tried this code. I cant seem to get it to work. I originally tried saving your code as a 'Routine' but got an error message along the lines of 'You cant run a procedure inside a routine'. I then tried to run your code as a querie (I assume thats what I was suppose to do originally) but still errors.

Forgive my ignorance but please could you provide a bit more instruction!

[cereal]

Hello,

I've tested my solution before posting, it works fine or at least, in reference to your example query, it worked fine for me. I used a command line mysql client, in connection with MySQL server 5.5 a part this the code to execute is just that, as explained in my previous answer.

From this:

I originally tried saving your code as a 'Routine' but got an error message along the lines of 'You cant run a procedure inside a routine'.

I suppose you're using a third party software: is this correct? Can you tell us which one? From the error it seems a conflict between a dedicated form and the above code. Can you report exactly each error?

[wvoke9]

Hi,

Yes Im using WAMPSERVER and PHPmyadmin to run the SQL database. The error code is 1064 and is a SQL syntax error.

Hope that helps,

Will

[cereal]

Ok, with PHPMyAdmin it should work fine by injecting the code inside the SQL box. Try by running:

SHOW PROCEDURE STATUS;

It will list all your procedures. The error you are reporting referes to the SQL syntax, and there should be more information about the exact point in which the error occurs. For example if I miss the P of the previous command:

SHOW ROCEDURE STATUS;

I get the following:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ROCEDURE STATUS' at line 1

If you still have doubts post your precise code and also the exact error, not only the number, post all the line.

[wvoke9]

Hi,

Yeh the additional details says 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and then it lists all of the code' at line 1.

Thanks for your help.

[cereal]

near 'and then it lists all of the code' at line 1.

Right now it seems there's a problem inside the WHERE statements. If you show us your query it will probably be easier to help you.

[wvoke9]

Hi, my code looks like this.

    SELECT Candidate, QuizName, PercentageScore, PassFail
    CREATE PROCEDURE mytest(IN name VARCHAR (200))
    BEGIN
    SELECT * FROM Resultsets WHERE candidate = name
    END

And my error message is.

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE PROCEDURE mytest(IN name VARCHAR (200)) BEGIN SELECT * FROM Resultsets WH' at line 2.

[cereal]

Remove the first line, that select cannot be there, since this part:

CREATE PROCEDURE mytest(IN name VARCHAR (200))
BEGIN
SELECT * FROM Resultsets WHERE candidate = name
END

is used only to create the procedure. But even removing that line, it will not work. You have to set a delimiter different from the one used to define the query inside the procedure, usually you end the queries by adding a semicolon ;, so, in order to create the statement you have to change it:

delimiter //

Then you create the procedure:

CREATE PROCEDURE mytest(IN name VARCHAR (200))
BEGIN
SELECT * FROM Resultsets WHERE candidate = name;
END//

Note ; at the end of the query and // at the end of the procedure statement, after this you have to pull back the default delimiter:

delimiter ;

After this you can use call mytest('Sarah Smith'). If you need to set an output variable you need to change the procedure to something like this:

CREATE PROCEDURE mytest(IN name VARCHAR(200), OUT cname VARCHAR(200), OUT quiz VARCHAR(200), OUT score DECIMAL(5,2), OUT fail INT(3))
    BEGIN
    select Candidate INTO cname, QuizName INTO quiz, PercentageScore INTO score, PassFail INTO fail FROM Results WHERE Candidate = name limit 1;
    END//

So when you execute the procedure you write:

CALL mytest('Sarah Smith', @cname, @quiz, @score, @fail);

And then you can use the variables:

SELECT @cname, @quiz, @score, @fail;

Note:

• when using the OUT variables, you cannot return more then a row per query, so as in my example you need to use limit 1
• you cannot use the same name of the field as variable in the procedure, otherwise you get a NULL value
• the OUT variables needs to be of the same type of the expected results, so if in cname you save the Candidate field which is a varchar, you have to set varchar, and so on for time stamps, integers and binary fields.

For more information about procedures read the documentation link I pasted in my previous answer.

[wvoke9]

Hi I've tried the code, getting the same error. I think it might be something to do with the fact that 'END' does not appear to be a command, just text. It does not highlight like 'select' or 'where' etc.

Is there another termination I can use?

[cereal]

Not that I'm aware of. END is part of a compound statement:

• http://dev.mysql.com/doc/refman/5.0/en/begin-end.html

So, unless you're using something previous of MySQL 5.* I don't have any other ideas. I can only suggest you to run the code from a script, as PHP:

<?php

$dns = 'mysql:dbname=TEST;host=127.0.0.1';
$username = '';
$password = '';

$conn = new PDO($dns, $username, $password);

$conn->exec('CREATE PROCEDURE mytest(IN name VARCHAR (200)) BEGIN SELECT * FROM Results WHERE candidate = name; END;');

print_r($conn->errorInfo());

In this case you can avoid the delimiter, but it is not the correct syntax to use inside a MySQL client or from PHPMyAdmin (at least in the latest versions).