MySQL could not get correctly parameter values in PROCEDURE

Question

In this simple PROCEDURE, I want to execute INSERT method if @user_id is NULL or pass NULL. but below sql command for INSERT is not working correctly:

Mysql ERROR:

The following query has failed: "CALL `tsmsp_post_users`(@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8); "

MySQL said: #1048 - Column 'username' cannot be null

Execute procedure

SET @p0 = '';

SET @p1 = 'qweqweqw';

SET @p2 = '';

SET @p4 = '';

SET @p5 = '';

SET @p6 = '';

SET @p7 = '';

SET @p8 = '';

CALL `tsmsp_post_users` (
      @p0 , @p1 , @p2 , @p3 , @p4 , @p5 , @p6 , @p7 , @p8
);

My Procedure:

CREATE DEFINER=`root`@`localhost` 
    PROCEDURE `tsmsp_post_users`
      (IN `user_id` INT(5), IN `username` VARCHAR(30) CHARSET utf8, IN `password`  VARCHAR(50) CHARSET utf8, IN `user_type` ENUM('موظفی','ساعتی') CHARSET utf8, IN `active` BOOLEAN, IN `created_at` DATETIME, IN `updated_at` DATETIME, IN `edited_by` INT(5), IN `start_work` DATETIME)
    DETERMINISTIC
    COMMENT 'TEST'
BEGIN

DECLARE action CHAR(10);

IF @user_id = NULL THEN
    SET action = "INSERT";
ELSE        
        SET action = "UPDATE";
END IF;
        IF action = "INSERT"  THEN
                BEGIN
                    SELECT 'INSERT';
                        INSERT INTO `users` (
                                        `user_id` ,
                                        `username` ,
                                        `password` ,
                                        `user_type` ,
                                        `active` ,
                                        `created_at` ,
                                        `updated_at` ,
                                        `edited_by` ,
                                        `start_work`
                                        )
            VALUES (
                                        @user_id, 
                                        @username,
                                        @password,
                                        @user_type,
                                        @active,
                                        @created_at,
                                        @updated_at,
                                        @edited_by,
                                        @start_work
                                     ) ;
                END;
        ELSE
                BEGIN
                        SELECT 'UPDATE';
                        SELECT * from users;
                END;
        END IF;
END

whats this problem and how to resolv that? thanks

Answer 1

You can change your insert query by changing @ parameters to @p0, @p1, @p2... to map parameters correctly your procedure will execute correctly