[Mysql 5.1] Stored Procedures (variables)

[Christopher Thomas]

I'm trying to check whether or not phone numbers in a particular column have "0" at the start of them, within a stored procedure so to check i've written:

Code:
DELIMITER $$

DROP PROCEDURE IF EXISTS `jobrun` $$
CREATE PROCEDURE `jobrun`()
BEGIN

DECLARE jobname_p VARCHAR(45);
DECLARE jobphoneno_p VARCHAR(20);
DECLARE checknum VARCHAR(45);

SET @Checknumber = CONCAT("SELECT LEFT(",jobname_p,".",jobphoneno_p,", 1) INTO ",checknum," FROM ",jobname_p," LIMIT 10, 1;");
PREPARE Checknumber FROM @Checknumber;
EXECUTE Checknumber;

IF checknum = '0' THEN
-- DO SOMETHING
ELSE
-- DO SOMETHING ELSE
END IF;

END $$

DELIMITER ;


Which i hoped would retrieve the 10th record (far enough into the file not be a header, not far enough to be the end) from a dynamicaly named column (hence the messey setup) and return the first character from that record so i could then do a simple IF statement on the variable "checknum" to see if its 0 or not. Unfortunately it keeps returning NULL.

I've taken the query out of the CONCAT and ran it as a normal query (minus variables) and it returns fine, i know there isn't an issue with the variables because they're used in other areas of the procedure, i just can't seem to figure out what's causing the problem.



Edited 1 time(s). Last edit at 08/06/2008 02:43AM by Christopher Thomas.