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.