Function for converting comma-separated input parameter into rows of int in MySQL

Question

I have a stored procedure where I am passing a CSV list of my values to be inserted into a table by the procedure.

My sp is

DELIMITER $$
USE `btwa`$$
DROP PROCEDURE IF EXISTS `sp_AddRole`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_AddRole`(IN _rolename VARCHAR(50) , IN _permissionList VARCHAR(255))
BEGIN 

        DECLARE _roleId INT;        
        INSERT INTO roles(RoleName, CreatedOn)
            VALUES    (_rolename,CURDATE());            
        SET _roleId = LAST_INSERT_ID();         
        /*INSERT INTO role_permissions(FkRoleId,FKPermissionId)
        VALUES (_roleId, ...);*/here I want to insert values of _permissionList 

END$$
DELIMITER ;

How can I get rows of int from permissionList which I might pass like '1,2,3,4'

I have done development in SQL Server but not much in MySQL.

Kamran Shahid · Accepted Answer · 2014-08-06 07:33:40Z

1

I were able to done it via help of a forum post at http://www.slickdev.com/2008/09/15/mysql-query-real-values-from-delimiter-separated-string-ids/

My query looks like following

INSERT INTO role_permissions(FkRoleId,FKPermissionId)
        SELECT _roleId, permissionID FROM permissions WHERE FIND_IN_SET(permissionID, _permissionList);

answered Aug 6, 2014 at 7:33

Kamran Shahid

4,1747 gold badges61 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Function for converting comma-separated input parameter into rows of int in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related