Mysql Stored Procedures Dynamic Queries

Question

Hi guys, I've had quite a few problems and know that I can get some good answers here!

Ok kinda 2 part question.
Part 1 I'm doing some really big updating of data, kind rejiging the tables mostly. so the question is should I be using a mysql stored procedure or mysql/php like normal. I'm currently on the stored producure frame of mind. Reasons are

• a) Quicker
• b) No timeouts.

If anyone has any other opinions let me know.

P.S we are talking about a big heap of data. LIKE over 1.5 million rows

2nd part.
In stored procedures how do I make a query that will only return one row just give me that row. Also the query is a little dynamic so like

SET tag_query = concat('SELECT tag_id FROM tags WHERE tag = "',split_string_temp,'"');

Any clues?
I can't seem to find anything just easy about this language!

Thanks in advance for your help.

Richard

Answer 1

Hi there. Your question is a little vague, so I'll just respond to the one piece of code you included.

If you want to get a tag_id from a tag name, I would recommend a stored function instead of a stored procedure.

Something like this:

DELIMITER $$

DROP FUNCTION IF EXISTS GET_TAG_ID $$

CREATE FUNCTION GET_TAG_ID(P_TAG_NAME varchar(255)) RETURNS int
BEGIN
  DECLARE v_return_val INT;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_return_val = -1;

  IF (P_TAG_NAME IS NULL)
  THEN
    RETURN NULL;
  END IF;

  select tag_id
  into v_return_val
  from TAGS
  where tag = P_TAG_NAME;

  RETURN v_return_val;
END $$

DELIMITER ;

Answer 2

To update data once (not as a regular task) I would prefer using a gui admin like phpmyadmin or sqlyog issuing SQL commands directly (with a good backup of course!) as you can see the results quickly and don't need to worry with other things than your main task.