CODEWITHSUNDEEP
sqlstored-proceduresmariadbsql-insertrecursive-query
NMaria
NMaria

Reputation: 38

error in your SQL syntax, MariaDB server version for the right syntax to use near 'CREATE PROCEDURE pro()

DELIMITER $$
use adventureworks
CREATE PROCEDURE pro()
BEGIN
    DECLARE i int DEFAULT 0;
    
    WHILE i <= 50 DO
        INSERT INTO salesorderheader(SalesOrderID, RevisionNumber, OrderDate)
        VALUES (i, 1, "2001-07-01 00:00:00");
        SET i = i + 1;
    END WHILE;
END $$
call pro()
DELIMITER ;

Upvotes: 0

Views: 1011

Answers (1)

GMB
GMB

Reputation: 222702

You need a terminator after each statement. The use and call statement don’t have one.

You are switching the delimiter at the beginning of your script, so the syntax would be:

delimiter $$

use adventureworks$$

create procedure pro()
begin 
    ...
end$$

call pro()$$

delimiter ;

A more typical approach is to change the delimiter for the procedure definition only:

use adventureworks;

delimiter $$
create procedure pro()
begin 
    ...
end$$
delimiter ;

call pro();

Side note: if you are running MariaDB 10.3 or higher, you can use a recursive query instead of a loop:

insert into salesorderheader(SalesOrderID, RevisionNumber, OrderDate)
with cte as (
    select 1 as i 
    union all select i + 1 from cte where i < 50
)
select i, 1, '2001-07-01'
from cte

Upvotes: 1

Related Questions