Stored Procedure

Writing external scripts to perform complex data handling is a tedious affair. The best way to automate tasks straightaway into the server is by using Stored Procedures. It is very useful to make them as flexible as possible, as it facilitates easy identification of any errors and can be used for executing a variety of tasks as well.

What are Stored Procedures?

Stored procedures are set of SQL commands that are stored in the database data server. After the storing of the commands is done, the tasks can be performed or executed continuously, without being repeatedly sent to the server. This also helps in decreasing the traffic in the networks and also reduces the CPU load.

There are many advantages of using stored procedures, which include:

• The functionality is application and platform related

• Functionality has to be developed only once, and all applications can call the same commands

• Task execution becomes easier and less complicated

• Network Traffic reduced to a greater extent

• Centralization of all commands made possible, which is helpful for various applications that repeatedly call the same set of complicated commands

• Runs on any kind of environment

DELIMITER $$ DROP PROCEDURE IF EXISTS `autions4wheelslive`.`addAuctionItemWatchList`$$ CREATE DEFINER=`root`@`%` PROCEDURE `addAuctionItemWatchList`( IN iAuction_item_id INT, IN iMember_id INT, IN iIs_active INT, OUT iErrorCode VARCHAR(50) ) BEGIN DECLARE iRecCount INT; SET iRecCount = 0; select count(*) into iRecCount from auction_watchlist where auction_item_id =iAuction_item_id and member_id=iMember_id and is_active=1; IF iRecCount = 0 THEN INSERT INTO auction_watchlist ( auction_item_id, member_id, add_date, is_active ) VALUES ( iAuction_item_id, iMember_id, now(), iIs_active ); SET iErrorCode =0; ELSE SET iErrorCode =1; END IF; END$$ DELIMITER ;

//Auction item class class auctionWatchList extends dbop{ //class properties - auction_item_det table public $iAuction_item_id; // item id public $iMember_id; // member id who is adding the item to hist watchlist public $iIs_active; public $iError;// error code //class methods public function addUpdateWatchList(){ //Function to insert/update auction item details $sql = "call addAuctionItemWatchList( '$this->iAuction_item_id', '$this->iMember_id', '$this->iIs_active', @out_param )"; $aData = $this->Execute($sql); $result = mysql_query('select @out_param as ERR'); $aData =mysql_fetch_assoc($result); $iError = $aData['ERR']; return $iError; }// eof insertUpdateAuctionItem }//eof class

DELIMITER $$ DROP PROCEDURE IF EXISTS `autions4wheelslive`.`addUpdateAuctionItemBid`$$ CREATE DEFINER=`root`@`%` PROCEDURE `addUpdateAuctionItemBid`( IN iAuction_item_id INT, IN iMember_id INT, IN iBid_amount INT, IN iBid_quantity INT, IN iBid_status INT, OUT iErrorCode VARCHAR(50) ) BEGIN DECLARE iRecCount INT; SET iRecCount = 0; select count(*) into iRecCount from item_bids where auction_item_id =iAuction_item_id and member_id=iMember_id and bid_status=1; IF iRecCount>0 THEN UPDATE item_bids SET bid_status=0 WHERE auction_item_id =iAuction_item_id and member_id=iMember_id; END IF; INSERT INTO item_bids ( auction_item_id, member_id, bid_amount, bid_quantity, bid_date, bid_status ) VALUES ( iAuction_item_id, iMember_id, iBid_amount, iBid_quantity, now(), iBid_status ); SET iErrorCode =0; END$$ DELIMITER ;

public function addUpdateAuctionBid(){ //Function to insert/update auction item bid details $sql = "call addUpdateAuctionItemBid( '$this->auction_item_id', '$this->member_id', '$this->bid_amount', '$this->bid_quantity', '$this->bid_status', @out_param )"; $aData = $this->Execute($sql); $result = mysql_query('select @out_param as ERR'); $aData =mysql_fetch_assoc($result); $iError = $aData['ERR']; return $iError; }// eof insertUpdateAuctionItem

DELIMITER $$ DROP PROCEDURE IF EXISTS `autions4wheelslive`.`insUpd_auctionItem`$$ CREATE DEFINER=`root`@`%` PROCEDURE `insUpd_auctionItem`( IN iAuction_item_id INT, IN sItem_name NVARCHAR(50), IN sItem_details NVARCHAR(5000), IN iCategory_id INT, IN iAdd_cat_id INT, IN sAuction_type NVARCHAR(50), IN iItem_qty INT, IN iStart_price INT, IN iReserve_price INT, IN iBuy_now_price INT, IN iBid_inc_option INT, IN iBid_increment INT, IN sStart_time DATETIME, IN sBid_duration DATETIME, IN iIs_auction_private INT, IN iAccept_swap_offer INT, IN sMain_picture NVARCHAR(50), IN sPic_gallery1 NVARCHAR(50), IN sPic_gallery2 NVARCHAR(50), IN sPic_gallery3 NVARCHAR(50), IN sZip_code NVARCHAR(10), IN iMember_id INT, IN iIs_active INT, OUT iErrorCode VARCHAR(50), OUT iAuctionId INT ) BEGIN DECLARE iRecCount INT; DECLARE iAuctionItemId INT; SET iErrorCode = -1; IF iAuction_item_id = 0 THEN select count(*) into iRecCount from auction_item_det where item_name=sItem_name and category_id=iCategory_id and member_id=iMember_id; ELSE /*select count(*) into iRecCount from auction_item_det where item_name=sItem_name and category_id<>iCategory_id and member_id=iMember_id;*/ SET iRecCount = 0; END IF; IF iRecCount > 0 THEN SET iErrorCode = 1; ELSE IF iAuction_item_id = 0 THEN Insert into auction_item_det ( item_name, item_details, category_id, add_cat_id, auction_type, item_qty, start_price, reserve_price, buy_now_price, bid_inc_option, bid_increment, start_time, bid_duration, is_auction_private, accept_swap_offer, main_picture, pic_gallery1, pic_gallery2, pic_gallery3, zip_code, member_id, is_active, add_date ) value ( sItem_name, sItem_details, iCategory_id, iAdd_cat_id, sAuction_type, iItem_qty, iStart_price, iReserve_price, iBuy_now_price, iBid_inc_option, iBid_increment, sStart_time, sBid_duration, iIs_auction_private, iAccept_swap_offer, sMain_picture, sPic_gallery1, sPic_gallery2, sPic_gallery3, sZip_code, iMember_id, iIs_active, now() ); SELECT max(auction_item_id) into iAuctionItemId from auction_item_det; SET iAuctionId = iAuctionItemId; ELSE UPDATE auction_item_det SET item_name=sItem_name, item_details=sItem_details, category_id=iCategory_id, add_cat_id=iAdd_cat_id, auction_type=sAuction_type, item_qty=iItem_qty, start_price=iStart_price, reserve_price=iReserve_price, buy_now_price=iBuy_now_price, bid_inc_option=iBid_inc_option, bid_increment=iBid_increment, start_time=sStart_time, bid_duration=sBid_duration, is_auction_private=iIs_auction_private, accept_swap_offer=iAccept_swap_offer, zip_code=sZip_code, member_id=iMember_id, is_active=iIs_active WHERE auction_item_id=iAuction_item_id; if (sMain_picture <> '1') THEN UPDATE auction_item_det SET main_picture=sMain_picture WHERE auction_item_id=iAuction_item_id; END IF; if (sPic_gallery1 <> '1') THEN UPDATE auction_item_det SET pic_gallery1=sPic_gallery1 WHERE auction_item_id=iAuction_item_id; END IF; if (sPic_gallery2 <> '1') THEN UPDATE auction_item_det SET pic_gallery2=sPic_gallery2 WHERE auction_item_id=iAuction_item_id; END IF; if (sPic_gallery3 <> '1') THEN UPDATE auction_item_det SET pic_gallery3=sPic_gallery3 WHERE auction_item_id=iAuction_item_id; END IF; SET iAuctionId = iAuction_item_id; END IF; SET iErrorCode = '0'; END IF; END$$ DELIMITER ;

public function insertUpdateAuctionItem() { //Function to insert/update auction item details $this->sItem_name = custom_addslashes($this->sItem_name); $this->sItem_details = custom_addslashes($this->sItem_details); //echo "asdfsdf".$this->$iAuction_item_id."~~~".$this->$sItem_name; $sql = "call insUpd_auctionItem( '$this->iAuction_item_id', '$this->sItem_name', '$this->sItem_details', '$this->iCategory_id', '$this->iAdd_cat_id', '$this->sAuction_type', '$this->iItem_qty', '$this->iStart_price', '$this->iReserve_price', '$this->iBuy_now_price', '$this->iBid_inc_option', '$this->iBid_increment', '$this->sStart_time', '$this->sBid_duration', '$this->iIs_auction_private', '$this->iAccept_swap_offer', '$this->sMain_picture', '$this->sPic_gallery1', '$this->sPic_gallery2', '$this->sPic_gallery3', '$this->sZip_code', $this->iMember_id, $this->iIs_active, @out_param, @iAuctionId )"; $aData = $this->Execute($sql); $result = mysql_query('select @out_param as ERR'); $aData =mysql_fetch_assoc($result); $this->iError = $aData['ERR']; $result = mysql_query('select @iAuctionId as Aid'); $aData =mysql_fetch_assoc($result); $this->iAuction_item_id = $aData['Aid']; //echo $this->iAuction_item_id; }// eof insertUpdateAuctionItem

DELIMITER $$ DROP PROCEDURE IF EXISTS `autions4wheelslive`.`proc_admin`$$ CREATE DEFINER=`root`@`%` PROCEDURE `proc_admin`( IN iAdminId INT, IN sFirst_name VARCHAR(255), IN sLast_name VARCHAR(255), IN sEmail VARCHAR(255), IN sUsername VARCHAR(255), IN set_password VARCHAR(255), IN sAdddate DATETIME, IN iAdded_by_id INT, IN sEditdate DATETIME, OUT iErrorCode VARCHAR(50) ) BEGIN DECLARE iUsernameRecCount INT; DECLARE iEmailRecCount INT; DECLARE iAdminRightId INT; DECLARE iAdd INT; DECLARE iEdit INT; DECLARE iDelete INT; DECLARE iView INT; SET iErrorCode = -1; IF iAdminId = 0 THEN select count(*) into iUsernameRecCount from admin_master where username=sUsername and is_active<>2; select count(*) into iEmailRecCount from admin_master where email=sEmail and is_active<>2; IF iUsernameRecCount > 0 AND iEmailRecCount > 0 THEN SET iErrorCode = 3; ELSEIF iEmailRecCount > 0 THEN SET iErrorCode = 2; ELSEIF iUsernameRecCount > 0 THEN SET iErrorCode = 1; END IF; ELSE select count(*) into iUsernameRecCount from admin_master where username=sUsername and admin_id<>iAdminId and is_active<>2 ; select count(*) into iEmailRecCount from admin_master where email=sEmail and admin_id<>iAdminId and is_active<>2; IF iUsernameRecCount > 0 AND iEmailRecCount > 0 THEN SET iErrorCode = 3; ELSEIF iUsernameRecCount > 0 THEN SET iErrorCode = 2; ELSEIF iEmailRecCount > 0 THEN SET iErrorCode = 1; END IF; END IF; IF iErrorCode <> IF iAdminId = 0 THEN insert into admin_master (username, password, first_name, last_name, email, add_date, added_by_id ) values (sUsername, set_password, sFirst_name, sLast_name, sEmail, sAdddate, iAdded_by_id ); ELSEIF iAdminId > 0 THEN update admin_master set username=sUsername, password=set_password, first_name=sFirst_name, last_name=sLast_name, email=sEmail, edit_date=sEditdate where admin_id=iAdminId; END IF; SET iErrorCode = 0; END IF; END$$ DELIMITER ;

$id = makeDatabaseValue($_POST['admin_id']); $first_name = makeDatabaseValue($_POST['first_name']); $last_name = makeDatabaseValue($_POST['last_name']); $email = makeDatabaseValue($_POST['email']); $loginid = makeDatabaseValue($_POST['login_id']); $password = makeDatabaseValue($_POST['password']); $sEditDate = date("Y-m-d H:i:s"); $strInsert = "call proc_admin($id,'$first_name','$last_name','$email','$loginid', '$password','','$iAdminId','$sEditDate', @out_param)"; $section = 'Edit Admin - Administration'; $db->Execute($strInsert); $result = $db->Execute('select @out_param as ERR'); if($result->MoveNext()) { $iErrorCode = $result->ERR; } if($iErrorCode == '1') { $msg = "Login ID already exists"; } elseif($iErrorCode == '2') { $msg = "E-mail Address already exists"; } elseif($iErrorCode == '3') { $msg = "Login ID and Email Address already exists"; } elseif($iErrorCode == '0') { $msg = "Admin has been updated successfully"; //postPageMessage('admin_edit.php?id='.$iAdminId,$msg); //exit; }

DELIMITER $$ DROP PROCEDURE IF EXISTS `autions4wheelslive`.`insUpd_auctionItemPayDet`$$ CREATE DEFINER=`root`@`%` PROCEDURE `insUpd_auctionItemPayDet`( IN iAuction_item_id INT, IN iIs_active INT, IN iShip_condition INT, IN iPayment_mode NVARCHAR(100), IN sDirect_pay_email NVARCHAR(50), IN iPostage_cost INT, IN iInsurance INT, IN iService INT, OUT iErrorCode VARCHAR(50) ) BEGIN DECLARE iRecCount INT; SET iRecCount = 0; select count(*) into iRecCount from auction_item_pay_det where auction_item_id =iAuction_item_id; IF iRecCount = 0 THEN INSERT INTO auction_item_pay_det ( auction_item_id, ship_condition, payment_mode, direct_pay_email, postage_cost, insurance, service ) VALUES ( iAuction_item_id, iShip_condition, iPayment_mode, sDirect_pay_email, iPostage_cost, iInsurance, iService ); ELSE UPDATE auction_item_pay_det SET ship_condition=iShip_condition, payment_mode=iPayment_mode, direct_pay_email=sDirect_pay_email, postage_cost=iPostage_cost, insurance=iInsurance, service=iService WHERE auction_item_id=iAuction_item_id; END IF; UPDATE auction_item_det SET is_active=iIs_active WHERE auction_item_id=iAuction_item_id; SET iErrorCode =0; END$$ DELIMITER ;

public function insertUpdateAuctionItemPayDet(){ //Function to insert/update auction item payment details $sql = "call insUpd_auctionItemPayDet( '$this->iAuctionItemIdForPaydetails', $this->iIs_active, '$this->iShip_condition', '$this->iPayment_mode', '$this->sDirect_pay_email', '$this->iPostage_cost', '$this->iInsurance', '$this->iService', @out_param )"; $aData = $this->Execute($sql); $result = mysql_query('select @out_param as ERR'); $aData =mysql_fetch_assoc($result); $this->iError = $aData['ERR']; }// eof insertUpdateAuctionItemPayDet