[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

mysql stored procedure

Posted on 2009-05-13
1
Medium Priority
?
744 Views
Last Modified: 2012-05-06
i have a table 'student'
i typed use my_dbname;

using sqlyog

CREATE PROCEDURE count_students_by_sex (OUT p_male INT, OUT p_female INT)
BEGIN
SELECT COUNT(*) FROM student WHERE sex = 'M' INTO p_male;
SELECT COUNT(*) FROM student WHERE sex = 'F' INTO p_female;
END;

i get error
Error Code : 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 '' at line 3

Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

Error Code : 1327
Undeclared variable: p_female

Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

Error Code : 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 'END' at line 1

Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000


i do not understand how to type stored procedure in sqlyog query editor
0
Comment
Question by:shopjd
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24379590
not sure, but you might need to use the DELIMETER stuff:
http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

also, you can run the 2 counts in 1 statement:
DELIMITER $$$
CREATE PROCEDURE count_students_by_sex (OUT p_male INT, OUT p_female INT)
BEGIN
SELECT SUM(CASE WHEN sex = 'M' THEN 1 ELSE 0 END)
     , SUM(CASE WHEN sex = 'F' THEN 1 ELSE 0 END)
  FROM student 
  INTO p_male, p_female;
END;
$$$
DELIMITER ;

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
What we learned in Webroot's webinar on multi-vector protection.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question