gsirianni gsirianni - 4 months ago 9
MySQL Question

mysql stored procedure declaration throws error on execution

I"m continiously receiving this error when trying to create this stored procedure. I'm trying to write a procedure that splits a comma delimited string. Similar to explode. I feel I'm close.

This is the error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE start_pos, end_pos INT;
SET start_pos = 1;
SET end_pos = Locat' at line 6

I copied the logic from a SQL Server example and did my best translate it to MySql syntax.

This is the entire procedure from start to finish. I'm hoping a well trained eye can explain why I'm getting an error.

DELIMITER $$

CREATE procedure split_string (in p_string_to_split VARCHAR(255),in p_delimiter CHAR(1) )
BEGIN
DROP TEMPORARY TABLE IF EXISTS split_channel_ids;
CREATE TEMPORARY TABLE split_channel_ids (p_channel_id int);

DECLARE start_pos, end_pos INT;
SET start_pos = 1;
SET end_pos = Locate(p_delimiter, p_string_to_split);
WHILE (start_pos < CHAR_LENGTH(p_string_to_split) + 1) DO
IF (end_pos = 0) THEN
SET end_pos = CHAR_LENGTH(p_string_to_split) + 1;
END IF;
--- INSERT split_channel_ids (p_channel_id)
--- VALUES(SUBSTRING(p_string_to_split, start_pos, end_pos - start_pos)) ;
SET start_pos = end_pos + 1;
SET end_pos = Locate(p_delimiter, p_string_to_split, start_pos);
END WHILE;
-- select * from imob_users;
select * from split_channel_ids;
END $$
DELIMITER ;



Answer

DECLARE statements (in MySQL) must be at the beginning of their enclosing BEGIN...END block.

In MS SQL, they can be anywhere; but it is annoying there because they do not have block scope, they have procedure scope, so you can't "re-use" names in independent blocks.

Comments