How to create stored procedures in SQL using cmd?

Question

This is what i tried. I have table called crop, and want to create stored procedure of selecting all in that table.

Here is the error i got:

MariaDB [sample]> CREATE PROCEDURE AllFarmers
    -> AS
    -> SELECT * FROM crop
    -> GO;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS SELECT * FROM crop GO' at line 2

Thank you for your help in advance

Looks to me like you're coming from the SQL Server world. The MySQL stored procedure syntax is different. Read this to see an example. dev.mysql.com/doc/connector-net/en/… Maddening, isn't it? — O. Jones
– O. Jones, Commented Sep 17, 2019 at 11:54

Andoni Alzelai · Accepted Answer · 2019-09-17 12:10:04Z

3

I saw that the syntax is not correct on your procedure. You should replace AS by BEGIN and GO by END.

I put an example here

delimiter //
CREATE PROCEDURE AllFarmers ()
BEGIN
SELECT * FROM Test;
END;
//
call allfarmers()

Try it and tell me if there is any problem

answered Sep 17, 2019 at 12:10

Andoni Alzelai

864 bronze badges

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

1 Comment

Raymond Nijland Over a year ago

CREATE PROCEDURE AllFarmers () SELECT * FROM Test; is the shortest you can write in MySQL and MariaDB.. As single statements does not need to use BEGIN / END blocks and also there for not need to use a DELIMITER

dmr · Accepted Answer · 2019-09-17 12:47:14Z

0

Might also be worth mentioning you can a drop to the start in case the stored procedure already exists:

DROP PROCEDURE IF EXISTS `AllFarmers`

answered Sep 17, 2019 at 12:47

dmr

192 bronze badges

Collectives™ on Stack Overflow

How to create stored procedures in SQL using cmd?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related