As is not valid identifier when creating procedure-CodePudding

I was following a tutorial on how to create procedures, I wrote the code

CREATE PROCEDURE test2
AS 
select * FROM vgsales_ratings;

and got

"AS" is not valid at this position expecting '('

I tried adding parentheses around some of the stuff but it didn't change anything. Is something wrong with my machine or is the code wrong. I'm using mysql workbench 8. The table exsits. Thanks for any help

CodePudding user response：

I would simply use :

CREATE PROCEDURE test2 ()
select * FROM vgsales_ratings;

But most of time,we use a routine block if we have multiple statements. In this case, delimiter needs to be used to change the default delimiter. After the procedure is created, change the delimiter back to ; .

delimiter //
CREATE PROCEDURE test2 ()
BEGIN
IF (select count(*) FROM vgsales_ratings) > 0 then
select * FROM vgsales_ratings;
end if;
END //
delimiter ;