[SOLVED] [Mysql 5.1] Stored Procedures (variables)

[Hooker]

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:

 

  DECLARE jobid_p INT;

      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;

 

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.

[Hooker]

Sorted it out myself

[fenway]

Sorted it out myself

How?

[Hooker]

first off i popped the wrong variable declaration in my example of what i was trying to do but beside that, i found this worked perfectly:

 

      SET @checknum NULL;
      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;