While Loop Within A Stored Procedure

ozzii · #1 (**permalink**) 03-30-09, 12:17

HI

I have a stored procedure which needs to be executed as two transactions or roll back if unsuccessful. However I have an input parameter called AccreditationIds which is in the format of a string e.g. ("1,2,4,6,8,..."). Each number in this string represent the primary key of a table called tbl_accreditations see below:

Code:

CREATE TABLE `tbl_accreditations` (
  `accreditation_id` int(10) unsigned NOT NULL auto_increment,
  `accreditation_name` varchar(100) NOT NULL,
  PRIMARY KEY  (`accreditation_id`),
  UNIQUE KEY `accreditation_name` (`accreditation_name`)
) ENGINE=InnoDB

the jobseeker details are stored in a table called tbl_jobeekers. Since a jobseeker can have multiple accreditations this info is stored in many to many table called tbl_jobseeker_accreditions - below:

HTML Code:

CREATE TABLE `tbl_jobseeker_accreditations` (
  `accreditation_id` int(10) unsigned NOT NULL,
  `js_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`accreditation_id`,`js_id`),
  FOREIGN KEY (`accreditation_id`) REFERENCES `tbl_accreditations` (`accreditation_id`),
  FOREIGN KEY (`js_id`) REFERENCES `tbl_jobseekers` (`js_id`) ON DELETE CASCADE
) ENGINE=InnoDB

Now the question is how do I parse this AccreditationIds string within a stored procedure and add a records to the tbl_jobseeker_accreditations? I have the procedure as below but unsure how to parse the AccreditationIds string and to iterate it

Code:

CREATE PROCEDURE jobseeker_add(IN inFname VARCHAR(50),IN inLname VARCHAR(50),
                               IN inAddLine VARCHAR(100), IN inCity VARCHAR(50),
                               IN inZip VARCHAR(50),IN inCountryId INT,
                               IN inPhone VARCHAR(50),IN inEmail VARCHAR(100),
                               IN inPwd VARCHAR(50), IN inAccreditationIds VARCHAR(50))

DECLARE jobseekerId INT;


BEGIN
start transaction; 
    -- Insert a new record into jobseeker and obtain the new jobseeker ID
    INSERT INTO tbl_jobseekers (js_fname, js_lname, js_addressline, 
	                        js_city, js_state, js_zip, js_country_id, 
                                js_phone, js_email,  js_pwd) 
          VALUES (inFname, inLname, inAddLine, inAddLine2 ,inCity, inZip,
                  inCountryId, inPhone, inEmail, inPwd);
		
    -- Obtain the new jobseeker ID
    SELECT LAST_INSERT_ID() INTO jobseekerId;
		
    -- unsure how to parse the inAccreditationIds
    WHILE inAccreditationIds
         INSERT INTO tbl_accreditations (accreditation_id, js_id)
              VALUES (inAccreditationIds, jobseekerId);
    END WHILE
commit;		
END

Any suggestions?

ozzii · #2 (**permalink**) 03-30-09, 16:16

OK I've done a bit of my own research and I've found the following function. Unfortunately its in T-SQL. Any body know if this would work in MySQL and how it could be implemented for the above scenario?

Code:

CREATE FUNCTION dbo.Parse ( @Array VARCHAR(1000), @separator VARCHAR(10)) 
RETURNS @resultTable TABLE 
	(parseValue VARCHAR(100))
AS
BEGIN

	DECLARE @separator_position INT 
	DECLARE @array_value VARCHAR(1000) 
	
	SET @array = @array + @separator
	
	WHILE patindex('%' + @separator + '%' , @array) <> 0 
	BEGIN
	
	  SELECT @separator_position =  patindex('%' + @separator + '%', @array)
	  SELECT @array_value = left(@array, @separator_position - 1)
	
		INSERT @resultTable
		VALUES (Cast(@array_value AS varchar))

	  SELECT @array = stuff(@array, 1, @separator_position, '')
	END

	RETURN
END

ozzii · #3 (**permalink**) 03-30-09, 16:40

OK I've done a bit of my own research and I've found the following function. Unfortunately its in T-SQL. Any body know if this would work in MySQL and how it could be implemented for the above scenario?

Code:

CREATE FUNCTION dbo.Parse ( @Array VARCHAR(1000), @separator VARCHAR(10)) 
RETURNS @resultTable TABLE 
	(parseValue VARCHAR(100))
AS
BEGIN

	DECLARE @separator_position INT 
	DECLARE @array_value VARCHAR(1000) 
	
	SET @array = @array + @separator
	
	WHILE patindex('%' + @separator + '%' , @array) <> 0 
	BEGIN
	
	  SELECT @separator_position =  patindex('%' + @separator + '%', @array)
	  SELECT @array_value = left(@array, @separator_position - 1)
	
		INSERT @resultTable
		VALUES (Cast(@array_value AS varchar))

	  SELECT @array = stuff(@array, 1, @separator_position, '')
	END

	RETURN
END

ozzii · #4 (**permalink**) 03-31-09, 08:26

Ok i've managed to knock up something to split the string and iterate it. See below. Does any body have a solution so that it could be implemented as a function instead of having to rewrite it each time you need to split a string to do multiple inserts?

Code:

CREATE PROCEDURE jobseeker_add(IN inFname VARCHAR(50),IN inLname VARCHAR(50),
                               IN inAddLine VARCHAR(100), IN inCity VARCHAR(50),
                               IN inZip VARCHAR(50),IN inCountryId INT,
                               IN inPhone VARCHAR(50),IN inEmail VARCHAR(100),
                               IN inPwd VARCHAR(50), IN inAccreditationIds VARCHAR(50))

DECLARE jobseekerId INT;
DECLARE delim VARCHAR(1);
DECLARE foundDelimPos tinyint unsigned;
DECLARE tmpStr VARCHAR(100);
DECLARE element VARCHAR(100);


BEGIN
start transaction; 
    -- Insert a new record into jobseeker and obtain the new jobseeker ID
    INSERT INTO tbl_jobseekers (js_fname, js_lname, js_addressline, 
	                        js_city, js_state, js_zip, js_country_id, 
                                js_phone, js_email,  js_pwd) 
          VALUES (inFname, inLname, inAddLine, inAddLine2 ,inCity, inZip,
                  inCountryId, inPhone, inEmail, inPwd);
		
    -- Obtain the new jobseeker ID
    SELECT LAST_INSERT_ID() INTO jobseekerId;
		
    -- assign accreditations string to temp string e.g. ("1,2,3,4,5")
    SET tmpStr = inAccreditationIds;

    -- set delimiter as a comma
    SET delim = ','; 
		
    -- find position of first occurence of comma in temp string
    SET foundDelimPos = instr(tmpStr,delim); 
		
    WHILE foundDelimPos <> 0 do
	set element = substring(tmpStr, 1, foundDelimPos-1);
	set tmpStr = substring(tmpStr, foundDelimPos+1); 
 				
 	INSERT INTO tbl_accreditations (accreditation_id, js_id) VALUES (element, jobseekerId);
				
	set foundDelimPos = instr(tmpStr,delim);
    END WHILE;

    -- insert last accreditation id in string
    IF tmpStr <> '' THEN
		INSERT INTO tbl_accreditations (accreditation_id, js_id) VALUES (tmpStr, jobseekerId);
    END IF

commit;		
END

Internet Services

Web Development

Digital Marketing

Consumer Tech