Error while declaring procedure



  • When I run this query

    it returns me a count of null values of the column country

    (select
        sum(case 
            when (country IS NULL) then 1 
            else 0 END)
        as 'null_flag'
    from city)
    

    whereas when I try to add the same code in a procedure like this

    DELIMITER $$
    

    CREATE PROCEDURE get_null_flags (col_name VARCHAR(40))
    BEGIN
    (select
    sum(case
    when ',[' + col_name + ']' IS NULL then 1
    else 0 END)
    as 'null_flag'
    from city)

    END $$

    DELIMITER ;

    It throws an error saying

    Query 1 ERROR: 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 'END' at line 10
    

    Note: I am using MySQL Ver 8.0.28 I have installed this version via Homebrew. I also have an older version of Mysql ie: 5.7 and I switch the versions using this documentation - https://jeanleem6.github.io/2019/05/08/Multiple-MySQL-Versions-with-Homebrew/



  • You need a semi-colon after the query. I.e.:

    DELIMITER $$
    

    CREATE PROCEDURE get_null_flags (col_name VARCHAR(40))
    BEGIN
    (select
    sum(case
    when ',[' + col_name + ']' IS NULL then 1
    else 0 END)
    as 'null_flag'
    from city);

    END $$

    DELIMITER ;

    By the way, you don't need to put the query inside a parenthesis.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2