How to check output of function or variable for NULL

Question

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;

Thorsten Dittmar · Accepted Answer · 2015-02-20 12:56:44Z

1

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;

edited Feb 20, 2015 at 12:56

answered Feb 20, 2015 at 12:51

Thorsten Dittmar

56.9k10 gold badges95 silver badges145 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dmitrij Kultasev Over a year ago

Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE test VARCHAR(1000)' at line 1

Dmitrij Kultasev Over a year ago

I updated the question. It is inside the stored proc. @ does not work either

Dmitrij Kultasev Over a year ago

Query: CREATE PROCEDURE sp_test_for_null() BEGIN DECLARE xml VARCHAR(1000); DECLARE test VARCHAR(1000); SET xml = ''; SET test = (SEL... Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; IF (test IS NULL) THEN SELECT 1; END IF; END' at line 6

Thorsten Dittmar Over a year ago

I updated again. It doesn't make sense to select nothing if test is not null.

Dmitrij Kultasev Over a year ago

I modified the question, there was [$@i], I put 1 there, but still error

Collectives™ on Stack Overflow

How to check output of function or variable for NULL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related