Recent News

Insert Comma Separated String to Database table in My SQL





 If the database table contains this id than it update the data if not contain it insert a new record which are not in table

 I am writing this for my requirement ,you can also change the input parameter as per your requirement.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `USP_Update_ServiceTask`(
   
        in  in_Action           varchar(10),
        in  PKService_Task_ID   int,
        in  in_serviceid        int,
        in  in_TaskIds          varchar(8000),
        in  in_Updatedby        varchar(50),
        out  message            varchar(50)

)
BEGIN
    DECLARE vDone tinyint(1) DEFAULT 1;
    DECLARE vIndex INT DEFAULT 1;
    DECLARE vSubString VARCHAR(15);
    DECLARE  vSeparator VARCHAR(5);
    DECLARE  count      INT;

       if in_Action='U' then
      
            SET vSeparator = ',';
            WHILE vDone > 0
                DO
                  SET vSubString = SUBSTRING(in_TaskIds, vIndex,
                                    IF(LOCATE(vSeparator, in_TaskIds, vIndex) > 0,
                                      LOCATE(vSeparator, in_TaskIds, vIndex) - vIndex,
                                      LENGTH(in_TaskIds)
                                    ));
                                   
                  IF LENGTH(vSubString) > 0 THEN
                                    
                      SET vIndex = vIndex + LENGTH(vSubString) + 1;
                     
                     SET count = (select count(*) from remotecorp_bpm.tbl_service_task where TAX_ID=vSubString  and Service_id=in_serviceid );             
                          if(count = 0) then
                         
                             INSERT INTO remotecorp_bpm.tbl_service_task(Service_id,TAX_ID,Created_Date,Created_By)
                             VALUES (in_serviceid,vSubString,now(),in_Updatedby);
                            
                               ELSE
                                   UPDATE remotecorp_bpm.tbl_service_task SET Service_id=in_serviceid,TAX_ID=vSubString,
                                   Modified_date=now(),Modified_By=in_Updatedby where TAX_ID=vSubString;
                            
                           end if; 
                     
                  ELSE
                      SET vDone = 0;
                  END IF;
 
  END WHILE;
 
         SET message='UP';
         
      
        end if;

END

Split Comma Separated String in My SQL


DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `String_Split`(
 vString VARCHAR(8000),
  vSeparator VARCHAR(5)
)
BEGIN
DECLARE vDone tinyint(1) DEFAULT 1;
DECLARE vIndex INT DEFAULT 1;
DECLARE vSubString VARCHAR(15);

DROP TABLE IF EXISTS tmpIDList;
CREATE TEMPORARY TABLE tmpIDList (ID INT);

WHILE vDone > 0
DO
  SET vSubString = SUBSTRING(vString, vIndex,
                    IF(LOCATE(vSeparator, vString, vIndex) > 0,
                      LOCATE(vSeparator, vString, vIndex) - vIndex,
                      LENGTH(vString)
                    ));
                   
                     IF LENGTH(vSubString) > 0 THEN
      SET vIndex = vIndex + LENGTH(vSubString) + 1;
      INSERT INTO tmpIDList VALUES (vSubString);
  ELSE
      SET vDone = 0;
  END IF;
 
  END WHILE;
 
END


you can call this for every string passed by fronend and use this any sql query ,After that you should drop the temp table
    like :  call  String_Split('1,1,1,12,3,34,22',',');
     select mem_name from member where Id in (select id from tmpIDList )
      DROP table tmpIDList;
   

Poll

Pages

Total Pageviews

ajaya rout. Powered by Blogger.

Followers

Popular Posts

Recent Comments | Recent Posts


Bloggerized by : GosuBlogger | designed by: Website Builder | Coded by: Blog Directory | Provided by: Wedding photojournalism chicago
bottom
Blogger Widgets