Posted by: prajapatinilesh | January 21, 2008

MySql Insert Stored procedure

create table catagory (
catagory_id int unsigned not null auto_increment,
name varchar(50) not null,
description text,
primary key (catagory_id)
) type=innodb;

create table catagory_set (
master_id int unsigned not null,
slave_id int unsigned not null,
index(master_id),
index(slave_id),
primary key (master_id,slave_id),
foreign key (master_id) references catagory (catagory_id) on delete cascade,
foreign key (slave_id) references catagory (catagory_id) on delete cascade
) type=innodb;

drop procedure add_catagory;
delimiter ?
create procedure add_catagory (IN param1 int, IN param2 char(50),
IN param3 text, OUT cid int, OUT error_msg char(80))
begin
declare master_id, master_exist, name_exist int;
set cid = -1;
set name_exist = 0, master_exist = 0;

# Insert a subcatagory #
if param1 > 0 then

# Check if the master catagory ID is valid #
select count(catagory_id), catagory_id into master_exist, master_id
from catagory where catagory_id=param1 group by catagory_id;

# Check if the same catagory name exist and the master catagory #
select count(catagory_id) into name_exist from catagory, catagory_set
where catagory.name=param2 and catagory.catagory_id=catagory_set.slave_id
and catagory_set.master_id=master_id;

if master_exist > 0 and name_exist = 0 then

lock tables catagory write, catagory_set write;
flush table catagory, catagory_set;
insert into catagory values (null, param2, param3);
select last_insert_id() into cid;
insert into catagory_set values (param1, cid);
unlock tables;

elseif master_exist = 0 then
set error_msg = ‘The master catagory ID provided does not exist’;

elseif name_exist > 0 then
set error_msg = ‘The catagory name already exist, please choose another name’;

end if;
# Insert a primary catagory #
else
# Search and compare the name of all primary catagory #
select count(catagory_id) into name_exist from catagory
where name = param2 and not exists(
select * from catagory_set
where catagory_set.slave_id = catagory.catagory_id
);
if name_exist > 0 then
set error_msg = ‘The catagory name already exist, please choose another name’;
else
insert into catagory values (null, param2, param3);
select last_insert_id() into cid;
end if;

end if;
end ?
delimiter ;

call add_catagory(1,’Planet’,’Earth’,@cid,@error);
select @cid, @error;

Advertisements

Responses

  1. Hi Nitesh

    Please Contact me….

  2. hii

    Mail me At Here

  3. please contact me…

  4. Error Code : 1314
    LOCK is not allowed in stored procedures


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: