how to handle no record found in mysql?

5.8k views Asked by At

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.

2

There are 2 answers

0
peterm On BEST ANSWER

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

0
kartavya On

First initialize v_type with any initial value OR use 'AS' instead of 'INTO' in the below query

SELECT IF(COUNT(TYPE) > 0, TYPE, NULL) 
INTO v_type
FROM timeperiodtype
WHERE projectid = 15;