Using Stored Procedures in MYSQL

Posted: April 28, 2010 in Uncategorized

Hi All,

I am really very glad to post this article. To avoid/restrict SQL injection, you must protect your SQL Codes (Specially DML Statements). It is similar to PL-SQL in SQL SERVER or ORACLE, with a little difference in syntax only. As you know, a procedure can take IN, OUT and INOUT parameters. Also, can return one or more values. I am providing a sample code, hope, it will help you;

CREATE DEFINER=`root`@`localhost` PROCEDURE `spr_country`(to_do varchar(20),cid integer,cname varchar(150),rid integer,out msg varchar(250),out result integer)
BEGIN
#Declaring Vriables
declare i int;
declare error int;
declare old_country_name varchar(150);
set error=0;
#Checking if empty
if trim(cname)=” then
set error=1;
set msg=’Region Name is found to be Empty !’;
set result=0;
end if;
if trim(rid)=” or trim(rid)=’0′  then
set error=1;
set msg=’Region Name is found to be Empty !’;
set result=0;
end if;
#For Task=add
if to_do=’add’ then
#checking if already exists
set i=(select count(*) from master_country where trim(lcase(country_name))=trim(lcase(cname)) and region_id=rid);
if i > 0 then
set error=1;
set msg=’COuntry Name Already Exists !’;
set result=0;
end if;
#Saving If No error
if error=0 then
insert into master_country(country_name,region_id,update_date) values(trim(cname),rid,now());
set msg=’New Country Saved Successfully !’;
set result=1;
end if;
end if;
#Add Task COmpleted
################################################################################
#For task=modify
#Find DUplicate Record If Exists ?
if to_do=’modify’ then
set old_country_name=(select country_name from master_country where country_id=cid);
set i=(select count(*) from master_country where trim(lcase(country_name))=trim(lcase(cname)) and trim(lcase(country_name)) != trim(lcase(old_country_name)));
if i > 0 then
set error=1;
set msg=’Country Name Already Exists !’;
set result=0;
end if;
if error=0 then
update master_country set country_name=trim(cname),region_id=rid,update_date=now() where country_id=cid;
set msg=concat(‘Region Successfully Updated to : ‘,cname);
set result=1;
end if;
end if;
#Modify Task Completed
#####################################################################################
#delete task
if to_do=’delete’ then
set i=(select count(*) from user_registration where country_id=cid);
if i > 0  then
set result=0;
set msg=’Sorry ! Country can not be deleted. Country in use …..’;
else
delete from master_country where country_id=cid;
set result=1;
set msg=’Country deleted from the database successfully !’;
end if;
end if;
#####################################################################################
#delete all
if to_do=’delete_all’ then
set i=(select count(*) from user_registration);
if i>0 then
set result=0;
set msg=’Sorry ! Some COuntries are in use for User Registration.’;
else
set result=1;
truncate table master_country;
end if;
end if;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `spr_country`(to_do varchar(20),cid integer,cname varchar(150),rid integer,out msg varchar(250),out result integer)BEGIN
#Declaring Vriablesdeclare i int;declare error int;declare old_country_name varchar(150);set error=0;
#Checking if emptyif trim(cname)=” thenset error=1;set msg=’Region Name is found to be Empty !’;set result=0;end if;
if trim(rid)=” or trim(rid)=’0′  thenset error=1;set msg=’Region Name is found to be Empty !’;set result=0;end if;
#For Task=addif to_do=’add’ then#checking if already existsset i=(select count(*) from master_country where trim(lcase(country_name))=trim(lcase(cname)) and region_id=rid);if i > 0 thenset error=1;set msg=’COuntry Name Already Exists !’;set result=0;end if;
#Saving If No errorif error=0 theninsert into master_country(country_name,region_id,update_date) values(trim(cname),rid,now());set msg=’New Country Saved Successfully !’;set result=1;end if;end if;#Add Task COmpleted#################################################################################For task=modify#Find DUplicate Record If Exists ?if to_do=’modify’ thenset old_country_name=(select country_name from master_country where country_id=cid);set i=(select count(*) from master_country where trim(lcase(country_name))=trim(lcase(cname)) and trim(lcase(country_name)) != trim(lcase(old_country_name)));if i > 0 thenset error=1;set msg=’Country Name Already Exists !’;set result=0;end if;
if error=0 thenupdate master_country set country_name=trim(cname),region_id=rid,update_date=now() where country_id=cid;set msg=concat(‘Region Successfully Updated to : ‘,cname);set result=1;end if;end if;#Modify Task Completed######################################################################################delete task if to_do=’delete’ then set i=(select count(*) from user_registration where country_id=cid); if i > 0  then set result=0; set msg=’Sorry ! Country can not be deleted. Country in use …..’; else delete from master_country where country_id=cid; set result=1; set msg=’Country deleted from the database successfully !’; end if; end if;######################################################################################delete all if to_do=’delete_all’ then set i=(select count(*) from user_registration); if i>0 then set result=0; set msg=’Sorry ! Some COuntries are in use for User Registration.’; else set result=1; truncate table master_country; end if; end if;END

Advertisements

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