dakait dakait - 7 months ago 12
SQL Question

Create Stored Procedure in MySQL

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..

Answer

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.