Stored procedure successful but doesn't run properly in mysql

Question

I have a stored procedure about 2 tables like this:

 CREATE DEFINER=`brambang`@`%` PROCEDURE `create_discount_campaign`(
          IN discount_campaign_discount_type_id CHAR(22),
          IN discount_campaign_product_id INT(10),
          IN discount_campaign_marketing_target_id INT(10),
          IN discount_campaign_max_use_per_user int(11),
          IN discount_campaign_discount_code varchar(100),
          IN discout_campaign_discount_amount decimal(10,0),
          IN discount_campaign_start_date datetime,
        IN discount_campaign_end_date datetime,
        IN discount_campaign_min_order_quantity INT(11),
        IN discount_campaign_min_order_price decimal(10,0),
        IN discount_campaign_discount_quota INT(11),
        IN discount_campaign_min_product_varian INT(11),
        IN discount_campaign_apply_all_product INT(10),
        IN discount_campaign_product_product_id int(10) unsigned,
        IN discount_campaign_product_active tinyint(1),
        IN discount_campaign_product_createdby int(11),
        IN discount_campaign_product_updatedby int(11),
        IN discount_campaign_product_category_id VARCHAR(100),
        IN discount_campaign_advanced_discount_advanced_rules_id int(50),
        IN discount_campaign_advanced_value varchar(255),
        IN discount_campaign_advanced_status tinyint(4)
    )
    proc:
        BEGIN
            DECLARE is_dc_discount_type_id INT(10);
            DECLARE is_dc_product_id INT(10);
            DECLARE is_dc_marketing_target_id INT(10);
            DECLARE is_dc_max_use_per_user INT(11);
            DECLARE is_dc_discount_code varchar(100);
            DECLARE is_dc_discount_amount decimal(10,0);
            DECLARE is_dc_start_date datetime;
            DECLARE is_dc_end_date datetime;
            DECLARE is_dc_min_order_quantity INT(11);
            DECLARE is_dc_min_order_price decimal(10,0);
            DECLARE is_dc_discount_quota INT(11);
            DECLARE is_dc_min_product_variant INT(11);
            DECLARE is_dc_apply_all_product INT(10);
            DECLARE is_dcp_product_id INT(10);
            DECLARE is_dcp_active tinyint(1);
            DECLARE is_dcp_createdby int(11);
            DECLARE is_dcp_updatedby int(11);
            DECLARE is_dca_discount_advanced_rules_id int(50);
            DECLARE is_dca_value varchar(255);
            DECLARE is_dca_status tinyint(4);
            DECLARE LAST_INSERT_ID int(11);
            DECLARE discount_campaign_product_category_id varchar(100);
            SET @querie = NULL;
            SET @querie2 = NULL;
            SET @querie3 = NULL;
            SET lc_time_names = "id_ID";
            SET @is_dc_discount_type_id = NULL;
            SET @is_dc_product_id = NULL;
            SET @is_dc_marketing_target_id = NULL;
            SET @is_dc_max_use_per_user = NULL;
            SET @is_dc_discount_code = NULL;
            SET @is_dc_discount_amount = NULL;
            SET @is_dc_start_date = NULL;
            SET @is_dc_end_date = NULL;
            SET @is_dc_min_order_quantity = NULL;
            SET @is_dc_min_order_price = NULL;
            SET @is_dc_discount_quota = NULL;
            SET @is_dc_min_product_variant = NULL;
            SET @is_dc_apply_all_product = NULL;
            SET @is_dcp_product_id = NULL;
            SET @is_dcp_active = NULL;
            SET @is_dcp_createdby = NULL;
            SET @is_dcp_updatedby = NULL;
            SET @is_dca_discount_advanced_rules_id = NULL;
            SET @is_dca_value = NULL;
            SET @is_dca_status = NULL;
            SET @LAST_INSERT_ID = NULL;
            SET @discount_campaign_product_category_id = NULL;
    
            
                SET @is_dc_discount_type_id = discount_campaign_discount_type_id;
                SET @is_dc_product_id = discount_campaign_product_id;
                SET @is_dc_marketing_target_id = discount_campaign_marketing_target_id;
                SET @is_dc_max_use_per_user = discount_campaign_max_use_per_user;
                SET @is_dc_discount_code = discount_campaign_discount_code;
                SET @is_dc_discount_amount = discout_campaign_discount_amount;
                SET @is_dc_start_date = discount_campaign_start_date;
                SET @is_dc_end_date = discount_campaign_end_date;
                SET @is_dc_min_order_quantity = discount_campaign_min_order_quantity;
                SET @is_dc_min_order_price = discount_campaign_min_order_price;
                SET @is_dc_discount_quota = discount_campaign_discount_quota;
                SET @is_dc_min_product_variant = discount_campaign_min_product_varian;
                SET @is_dc_apply_all_product = discount_campaign_apply_all_product;
                SET @discount_campaign_product_category_id = discount_campaign_product_category_id;
                
                SET @querie = CONCAT("
            INSERT INTO discount_campaigns (
              discount_type_id, 
              product_id, 
              marketing_target_id, 
             max_use_per_user, 
              discount_code, 
              discount_amount, 
              start_date,
                        end_date,
              min_order_quantity,
              min_order_price,
              discount_quota,
              min_product_variant,
              apply_all_products,
              createdAt,
              updatedAt
            ) VALUES (
              ?, 
              ?, 
              ?, 
              ?, 
              ?, 
              ?, 
              ?,
              ?,
              ?,
              ?,
              ?,
              ?,
              ?,
              NOW(),
              NOW()
            );
          ");
                
          PREPARE stmt FROM @querie;
    
          EXECUTE stmt 
          USING 
            @is_dc_discount_type_id,
              @is_dc_product_id, 
              @is_dc_marketing_target_id, 
             @is_dc_max_use_per_user, 
              @is_dc_discount_code, 
              @is_dc_discount_amount, 
              @is_dc_start_date,
                        @is_dc_end_date,
              @is_dc_min_order_quantity,
              @is_dc_min_order_price,
              @is_dc_discount_quota,
              @is_dc_min_product_variant,
              @is_dc_apply_all_product;
          
                DEALLOCATE PREPARE stmt;
                
             SET @is_dcp_product_id = discount_campaign_product_product_id;
             SET @is_dcp_active = discount_campaign_product_active;
             SET @is_dcp_createdby = discount_campaign_product_createdby;
             SET @is_dcp_updatedby = discount_campaign_product_updatedby;
             SET @LAST_INSERT_ID = LAST_INSERT_ID();
             
             IF (discount_campaign_apply_all_product = 0) THEN
              SET @querie2 = CONCAT("INSERT INTO discount_campaign_product (discount_campaign_id,
              product_id, 
              active, 
              createdby, 
             updatedby, 
              createdAt,
              updatedAt
            ) SELECT ?,product_id,?,?,NULL,NOW(),NULL 
            FROM product_categories WHERE category_id IN 
            (?) AND status=1");
                
          PREPARE stmt_2 FROM @querie2;
    
          EXECUTE stmt_2 
          USING 
            @LAST_INSERT_ID,
            @is_dcp_active, 
            @is_dcp_createdby, 
            @discount_campaign_product_category_id;
            DEALLOCATE PREPARE stmt_2;
           END IF;
    
    END

basically i have 2 tables discount_campaign (any variable with an alias dc are this table) and discount_campaign_product (any variable with an alias dcp are this table). This stored procedure has a function to insert data into table discount_campaign with the parameter which been made. in discount_campaign table there's column namely apply_all_product. if apply_all_product filled "0" in parameter, then another parameter (with alias dcp) will insert some data in discount_campaign_product table. but when i applied with apply_all_product = 0, this store procedure only insert data in discount_campaign table, not in discount_campaign_product table.

Where is my mistake in this stored procedure?

Answer 1

I'd guess you didn't see any rows inserted into discount_campaign_product because there were no matching rows in product_categories. You used INSERT...SELECT FROM product_categories but you search for rows where category_id IN (?).

I suppose you mean this to turn into category_id IN (1,2,3,4,5) but that's not how query parameters work in SQL. One parameter can only be used for one scalar value. So in your code, it will search as if you did category_id IN ('1,2,3,4,5') which searches for a single string. It might match the first number in the string as it casts the string to a numeric, but if there is no category matching that first number, then it'll find no matches.

The workaround is to use FIND_IN_SET(category_id, '1,2,3,4,5'). This is really not how the function is supposed to be used, but it works to locate an integer in a comma-separated list of integers. Be careful not to put spaces in between the list elements, because it won't work with FIND_IN_SET().

---

Other issues:

You DECLARE local variables then you don't use them. Understand that in MySQL, the variables myvar and @myvar are two different variables. You need to declare as local variables the type with no @ sigil. Whereas the type with the @ sigil are user-defined session variables, which you don't declare -- just by setting its value, the variable is implicitly created.

You are using PREPARE/EXECUTE but there is no need to use dynamic SQL in this case. Your only dynamic parts are values, so you can just use the input parameters for the procedure as if they are query parameters. They are guaranteed to act like a single scalar, and they won't cause SQL injection. You would need to use PREPARE/EXECUTE only if you had dynamic expressions or dynamic identifiers in your queries, but you don't.

The only risk to using your procedure parameters directly in queries is that you should make sure the parameter names are not the same as any column names of tables in your query.

DECIMAL(10,0) doesn't make much sense. Why use decimal with 0 digits of scale? That's just an integer.

INT(11) vs INT(50) makes no difference. The argument to the INT types does not change their size or range of values. In fact, using this argument for INT types is deprecated in MySQL 8.0, so you should get into the habit of removing it.

Here's the procedure I tested, and it works:

CREATE PROCEDURE create_discount_campaign(
    IN discount_campaign_discount_type_id CHAR(22),
    IN discount_campaign_product_id INT,
    IN discount_campaign_marketing_target_id INT,
    IN discount_campaign_max_use_per_user INT,
    IN discount_campaign_discount_code varchar(100),
    IN discout_campaign_discount_amount decimal(10,0),
    IN discount_campaign_start_date datetime,
    IN discount_campaign_end_date datetime,
    IN discount_campaign_min_order_quantity INT,
    IN discount_campaign_min_order_price decimal(10,0),
    IN discount_campaign_discount_quota INT,
    IN discount_campaign_min_product_varian INT,
    IN discount_campaign_apply_all_product INT,
    IN discount_campaign_product_product_id INT UNSIGNED, -- this is unused?
    IN discount_campaign_product_active TINYINT,
    IN discount_campaign_product_createdby INT,
    IN discount_campaign_product_updatedby INT,
    IN discount_campaign_product_category_id VARCHAR(100),
    IN discount_campaign_advanced_discount_advanced_rules_id INT,
    IN discount_campaign_advanced_value varchar(255),
    IN discount_campaign_advanced_status TINYINT
)
MODIFIES SQL DATA
BEGIN
    DECLARE last_id BIGINT;

    INSERT INTO discount_campaigns
    SET
      discount_type_id = discount_campaign_discount_type_id,
      product_id = discount_campaign_product_id,
      marketing_target_id = discount_campaign_marketing_target_id,
      max_use_per_user = discount_campaign_max_use_per_user,
      discount_code = discount_campaign_discount_code,
      discount_amount = discout_campaign_discount_amount,
      start_date = discount_campaign_start_date,
      end_date = discount_campaign_end_date,
      min_order_quantity = discount_campaign_min_order_quantity,
      min_order_price = discount_campaign_min_order_price,
      discount_quota = discount_campaign_discount_quota,
      min_product_variant = discount_campaign_min_product_varian,
      apply_all_products = discount_campaign_apply_all_product,
      createdAt = NOW(),
      updatedAt = NOW();

    IF (discount_campaign_apply_all_product = 0) THEN
        SET last_id = LAST_INSERT_ID();
        INSERT INTO discount_campaign_product (discount_campaign_id,
          product_id, active, createdby, updatedby, createdAt, updatedAt)
        SELECT last_id, c.product_id, discount_campaign_product_active,
          discount_campaign_product_createdby, NULL, NOW(), NULL
        FROM product_categories AS c
        WHERE FIND_IN_SET(c.category_id, discount_campaign_product_category_id)
          AND c.status=1;
    END IF;
END

Notice I used a different syntax for the first INSERT. This is a MySQL extension to standard SQL, and it only works for single-row inserts. But it's very handy, because it makes it easier. In standard INSERT syntax with the column list and then a VALUES clause, I find it's difficult to make sure I have matched the columns to the right values. The INSERT ... SET syntax is more readable.