Search code examples
mysqlsqlstored-proceduresno-data

how to handle no record found in mysql?

I have a procedure with select statement below:

SELECT TYPE 
INTO v_type
FROM timeperiodtype
WHERE projectid = 15;

I have no record with projectid =15. So no record will retrieve. Is there any way I can get null instead of nothing from this query if I dont have record for 15.

I have already tried ifnull, nullif and I can use declare handler but I am using it with cursor. Is it possible to use the same declare handler for this query if yes then please explain with some example.

Solution

  • Explicitly initialize v_type with NULL prior to executing SELECT INTO

    SET v_type = NULL;
SELECT TYPE 
  INTO v_type
  FROM timeperiodtype
 WHERE projectid = 15;

    or just use SET statement

    SET v_type = 
(
  SELECT TYPE 
    FROM timeperiodtype
   WHERE projectid = 15;
);

    Here is SQLFiddle demo