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. https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html Maddening, isn't it? — O. Jones, Sep 17 '19 at 11:54

score 1 · Accepted Answer · answered Sep 17 '19 at 12:10

1

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 '19 at 12:10

Andoni Alzelai

66
4

`CREATE PROCEDURE AllFarmers () SELECT * FROM Test;` is the [shortest](https://www.db-fiddle.com/f/oYttLueYaRAu9CyZn2xJ5J/0) 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` – Raymond Nijland Sep 17 '19 at 12:31

score 0 · Answer 2 · answered Sep 17 '19 at 12:47

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 '19 at 12:47

dmr

9
2

How to create stored procedures in SQL using cmd?

2 Answers2