Getting errors while creating a procedure that involves insertion of values in Mariadb

Viewed 54

This is the table for which I am trying to make a procedure which will insert values in it

MariaDB [sql_proc]> desc VEHICLE_REGISTRATION;
+-----------------------+-------------+------+-----+---------+-------+
| Field                 | Type        | Null | Key | Default | Extra |
+-----------------------+-------------+------+-----+---------+-------+
| Registration_No       | varchar(50) | NO   | PRI | NULL    |       |
| Engine_No             | varchar(50) | NO   |     | NULL    |       |
| Make                  | varchar(50) | NO   |     | NULL    |       |
| Owner                 | varchar(50) | NO   |     | NULL    |       |
| Register_Amount       | float       | NO   |     | NULL    |       |
| Date_of_Registration  | date        | NO   |     | NULL    |       |
| Place_of_Registration | varchar(70) | NO   |     | NULL    |       |
+-----------------------+-------------+------+-----+---------+-------+
7 rows in set (0.007 sec)

This is the query that created the table above

CREATE TABLE VEHICLE_REGISTRATION(
    Registration_No VARCHAR(50) NOT NULL,
    Engine_No VARCHAR(50) NOT NULL, 
    Make VARCHAR(50) NOT NULL, 
    Owner VARCHAR(50) NOT NULL, 
    Register_Amount FLOAT NOT NULL,
    Date_of_Registration DATE NOT NULL,
    Place_of_Registration VARCHAR(70) NOT NULL,
    PRIMARY KEY(Registration_No)
);

This is the procedure I was trying to create in mariadb

delimiter //
Create PROCEDURE insert_vehicleinfo(
    IN Registration_No1 varchar(50), 
    IN Engine_No1 varchar(50),
    IN Make1 varchar(50), 
    IN Owner1 varchar(50), 
    IN Register_Amount1 FLOAT, 
    IN Date_of_Registration1 DATE, 
    IN Place_of_Registration1 varchar(70)
    )
    BEGIN
        INSERT INTO VEHICLE_REGISTRATION
                (Registration_No, Engine_No, Make, Owner, 
                Register_Amount, Date_of_Registration,Place_of_Registration ) 
        VALUES (Registration_No1,Engine_No1, Make1, Owner1, Register_Amount1,
                Date_of_Registration1,Place_of_Registration1); 
end//

The above procedure gives the following error

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 'BEGIN INSERT INTO VEHICLE_REGISTRATION(Registration_No,Engine_No, Make, Owne...' at line 2

However I have successfully created procedure for this table that involves insertion of 2 fields only

create procedure Proc1( 
    IN a DATE, 
    IN b varchar(60) 
    ) 
begin 
    insert into VEHICLE_REGISTRATION(Date_of_Registration, Place_of_Registration ) 
        values(a,b); 
end//                

Query OK, 0 rows affected (0.056 sec)

This also gives errors when I call it, but atleast a proc gets created

I get these errors when I try to call this one:

call Proc1("2022-10-2", "place");

ERROR 1364 (HY000): Field 'Registration_No' doesn't have a default value MariaDB [sql_proc]>

why this procedure gets created successfully while I fail to create one when all fields are given for insert?

I am able to create a procedure that inserts only one or two values but I fail to created a similar procedure that inserts every value

Edit

It's working now! Earlier it was giving errors. I just had to put everything (every parameter of the procedure) on a separate line and the errors went away.

0 Answers
Related