Create Stored Procedure in MySQL

Question

i have the following syntax for creating a stored procedure in MySQL 5.0.10

delimiter //
CREATE PROCEDURE getTweets (var1 varchar(100))
LANGUAGE SQL
SQL SECURITY DEFINER
COMMENT 'A procedure to return 20 least scored tweets based on the temp sessionid of the user'
BEGIN
    SELECT count(ts.tweetid) as "count",t.id as "tweetid", t.tweettext as "tweet"
    FROM tweets t
    LEFT JOIN tweetscores ts ON t.id = ts.tweetid
    where t.id not in (select distinct(tweetid) from tweetscores where  temp_sessionid=var1)
    group by t.id, t.tweettext order by count   
    LIMIT 10;
END//

i keep on getting the error

#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 12

can anybody spot the error..

@dakait: Remove the delimiter // part. In PHPMyAdmin you dont need it I think. — juergen d
– juergen d, Commented Jan 21, 2015 at 8:53

juergen d · Accepted Answer · 2015-01-21 09:04:13Z

3

Your query works when executing it as a script on DB level.

But using PhpMyAdmin you need remove the delimiter statement.

CREATE PROCEDURE getTweets (var1 varchar(100))
LANGUAGE SQL
SQL SECURITY DEFINER
COMMENT 'A procedure to return 20 least scored tweets based on the temp sessionid of the user'
BEGIN
    SELECT count(ts.tweetid) as "count",t.id as "tweetid", t.tweettext as "tweet"
    FROM tweets t
    LEFT JOIN tweetscores ts ON t.id = ts.tweetid
    where t.id not in (select distinct(tweetid) from tweetscores where  temp_sessionid=var1)
    group by t.id, t.tweettext order by count   
    LIMIT 10;
end

PhpMyAdmin will do the rest for you.

answered Jan 21, 2015 at 9:04

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dan H · Accepted Answer · 2016-04-21 10:49:34Z

0

CREATE PROCEDURE getTweets (var1 varchar(100))
LANGUAGE SQL
SQL SECURITY DEFINER
COMMENT 'A procedure to return 20 least scored tweets based on the temp sessionid of the user'
BEGIN
    SELECT count(ts.tweetid) as "count",t.id as "tweetid", t.tweettext as "tweet"
    FROM tweets t
    LEFT JOIN tweetscores ts ON t.id = ts.tweetid
    where t.id not in (select distinct(tweetid) from tweetscores where  temp_sessionid=var1)
    group by t.id, t.tweettext order by count   
    LIMIT 10;
end

edited Apr 21, 2016 at 10:49

Dan H

3,6313 gold badges38 silver badges47 bronze badges

answered Apr 21, 2016 at 10:21

ron.p

11 bronze badge

Collectives™ on Stack Overflow

Create Stored Procedure in MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related