CODEWITHSUNDEEP
mysql
Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5767

How to check output of function or variable for NULL

I am bit confused with Mysql syntax. I want to check for NULL the value of ExtractValue(xml, '//order[1]/quantity[$@i]') function. It can be assign to variable or this action can be skipped. I tried this and there is syntax error:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_test_for_null`$$

CREATE PROCEDURE `sp_test_for_null`()
BEGIN
DECLARE xml VARCHAR(1000);
SET xml = '';
DECLARE test VARCHAR(1000);
SET test = (SELECT ExtractValue(xml, '//order[1]/quantity[$@i]');
IF (test IS NULL) THEN SELECT 1; END IF;
END$$

DELIMITER ;

CALL sp_test_for_null;

Upvotes: 0

Views: 27

Answers (1)

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56717

I'd try this (note that all DECLAREs are at the beginning:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_test_for_null`$$

CREATE PROCEDURE `sp_test_for_null`()
BEGIN
    DECLARE xml VARCHAR(1000);
    DECLARE test VARCHAR(1000);

    SET xml = '';
    SET test = (SELECT ExtractValue(xml, '//order[1]/quantity[$@i]');
    SELECT ISNULL(test, 1, 0);
END$$

DELIMITER ;

CALL sp_test_for_null;

Upvotes: 1

Related Questions