CODEWITHSUNDEEP
mysqlsqlstored-proceduresno-data
Aamir
Aamir

Reputation: 758

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.

Upvotes: 0

Views: 6110

Answers (2)

kartavya
kartavya

Reputation: 342

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;

Upvotes: 0

peterm
peterm

Reputation: 92805

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

Upvotes: 2

Related Questions