SQL stored procedure, variable is null

Question

this is my stored procedure:

CREATE PROCEDURE `insert_exercise` (nazev VARCHAR(45), URL VARCHAR(255), obtiznost INT, partie INT)

BEGIN
    DECLARE video_id INT;
    DECLARE cvik_id INT;

    INSERT INTO `odkaz_video` (URL) VALUES (@URL);
    SELECT id INTO video_id FROM `odkaz_video` WHERE URL = @URL;
    INSERT INTO `cvik` (nazev, odkaz_video, obtiznost_id) VALUES (@nazev, video_id, @obtiznost);
    SELECT id INTO cvik_id FROM `cvik` WHERE nazev = @nazev;
    INSERT INTO `cvik_partie` (partie_id, cvik_id) VALUES (@partie, cvik_id);
END

When I call this SP I get error 1048, Column 'URL' cannot be null. Why is this variable (@URL) null even though I typed in all parameters?

Thanks for the respond

You might also find this answer helpful: http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference — zedfoxus, Jan 08 '14 at 20:43
Can you please include the exact statement you are using to call the stored procedure, as well as the full table definitions of the tables involved? It's possible the problem is either in the calling statement or the result of a key constraint in the table definitions. — Gerf, Jan 08 '14 at 20:43

score 1 · Accepted Answer · answered Jan 08 '14 at 20:42

1

Use the names as given in the procedure parameters:

CREATE PROCEDURE insert_exercise (nazev VARCHAR(45), URL VARCHAR(255), obtiznost INT, partie INT)

They are not prefixed with a @.

Check out this tutorial: MySQL stored procedure parameters

answered Jan 08 '14 at 20:42

FriedBeans

36
5

score 0 · Answer 2 · answered Jan 08 '14 at 20:45

@URL is NULL because you never set its value. The input parameter is named URL, not @URL.

Furthermore I recommend that you prefix the parameter names with p_ and the local variable names with v_ to differentiate them from the column names.

For example:

CREATE PROCEDURE `insert_exercise` (p_nazev VARCHAR(45), p_URL VARCHAR(255), p_obtiznost INT, p_partie INT)

BEGIN
    DECLARE v_video_id INT;
    DECLARE v_cvik_id INT;

    INSERT INTO `odkaz_video` (URL) VALUES (p_URL);
    SELECT id INTO v_video_id FROM `odkaz_video` WHERE URL = p_URL;
    INSERT INTO `cvik` (nazev, odkaz_video, obtiznost_id) VALUES (p_nazev, v_video_id, p_obtiznost);
    SELECT id INTO v_cvik_id FROM `cvik` WHERE nazev = p_nazev;
    INSERT INTO `cvik_partie` (partie_id, cvik_id) VALUES (p_partie, v_cvik_id);
END

SQL stored procedure, variable is null

2 Answers2