Advertisement
AmourSpirit

MySql - stored proc to take array

Jul 18th, 2015
269
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 0.75 KB | None | 0 0
  1. DROP procedure IF EXISTS `uh46v_order_status_array_to_table`;
  2.  
  3. DELIMITER $$
  4. USE `xhadmin_jmln3`$$
  5. CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `uh46v_order_status_array_to_table`(IN statuses varchar(255))
  6.     NO SQL
  7. BEGIN
  8. DROP TEMPORARY TABLE IF EXISTS oc_order_status_tmp;
  9.  
  10. SET @sql = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS `oc_order_status_tmp` (
  11.                     PRIMARY KEY (`order_status_id`)
  12.                     )  ENGINE=MyISAM
  13.                     AS (SELECT `order_status_id`, `name` FROM `oc_order_status` WHERE `language_id` = 1 AND `order_status_id` in (',
  14.                     statuses, '
  15.                     ))');
  16.  
  17. PREPARE stmt FROM @sql;
  18. EXECUTE stmt;
  19. DEALLOCATE PREPARE stmt;
  20. -- Usage: call uh46v_order_status_array_to_table('1,2,3,4,5');
  21. -- Usage: select * from oc_order_status_tmp;
  22. END$$
  23.  
  24. DELIMITER ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement