Stored procedures can be used to insert new values for more than one table.  This could be handy if the data is stored in few tables as well as restricting the inputs coming from the application layer.
The following example will show how to create a stored procedure for insert values in MySQL database. This can give the general idea of how to create a stored procedure which contains even some calculation made ‘on the fly’. After the procedure is created the separately issued insert statements┬ácan be replaced with a single line statement.

If you want to check the script just simply fire MySQL use a test database and copy and paste

-- create a table for products
drop table if exists prodPrice;
drop table if exists prodDesc;

create table prodPrice(
 id int unsigned not null primary key,
 netPrice decimal(8,2) not null,
 vat decimal(8,2) not null,
 totalPrice decimal(8,2) not null
);

create table prodDesc(
 id int unsigned not null auto_increment primary key,
 name varchar(50) not null unique,
 prodSpec varchar(3000) default 'Product not specified yet!',
 prodRev varchar(3000) default 'No reviews yet!'
);

-- create procedure for inserting multiple values
drop procedure if exists newProd;
drop procedure if exists delProd;

delimiter //
create procedure newProd(in name varchar(50),
 in netPrice decimal(8,2))
begin
 declare vat decimal(8,2) default 0.23;
 insert into prodDesc values(null, name, default, default);
 insert into prodPrice values(last_insert_id(),netPrice,(netPrice * vat),(netPrice*(1+vat)));
end;
//
-- procedure to quick remove from multiple table
create procedure delProd(in idd int)
begin
 delete from prodDesc where id = idd;
 delete from prodPrice where id = idd;
end;
//

delimiter ;

-- use the procedure to see it all works
call newProd('Laptop', 1400);
call newProd('TV', 1865);
call newProd('Modem', 85);

-- to see the velues in the tabels,
select proddesc.id, name, prodspec as 'Product Specification', prodrev as 'Product Review', netprice 'Net. Price', vat, totalprice as 'VAT + Net. Price'
 from proddesc
 inner join prodprice
 on proddesc.id = prodprice.id;
-- now let's delete
call delprod(2);

-- check the result
select * from proddesc; select * from prodprice;

Share This