How to use CREATE PROCEDURE?

[silvair]

Hi All,
How to use CREATE PROCEDURE, and I do not understand the distinction between a script and a query. I wrote this small script:

CREATE PROCEDURE SimpleProc (OUT outCount INTEGER)
BEGIN
SELECT COUNT(*) INTO outCount FROM searches;
END

On executing the script I get the baffling error message: "Line 1; 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 'SELECT COUNT (*) INTO outCount FROM searches' at line 3; ErrorNr. 1064". However, if I open up a query and copy in the exact same text I get the message: "Query returned no resultset", i.e. the call to CREATE PROCEDURE was successful.

Why should the same piece of SQL work as a query and fail as a script? any idea? -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[rian]

it's the result of a certain metapher used here. The default delimiter for queries is the semicolon so QB assumes your query ends after "... from searches;" which is obviously wrong. What you therefore need is temporarily setting a different delimiter (e.g. $), e.g.:

delimiter $
CREATE PROCEDURE SimpleProc (OUT outCount INTEGER)
BEGIN
SELECT COUNT(*) INTO outCount FROM searches;
END$
delimiter ; -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --