so i have 3 tables which i had to make a single store procedure like this
CREATE DEFINER=`brambang`@`%` PROCEDURE `create_discount_campaign2`(
IN discount_campaign_discount_type_id CHAR(22),
IN discount_campaign_product_id varchar(100),
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,
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, case when
find_in_set (c.product_id, discount_campaign_product_product_id) then 0 else 1 end
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;
SET
discount_advanced_rules_id = discount_campaign_advanced_discount_advanced_rules_id,
value = discount_campaign_advanced_value,
status = discount_campaign_advanced_status;
IF (discount_campaign_advanced_discount_advanced_rules_id = 0) THEN
INSERT INTO discount_campaign_advanced
SET
discount_campaign_id = last_id,
discount_advance_rules_id = discount_campaign_advanced_discount_advanced_rules_id,
value = discount_campaign_advanced_value,
createdAt = NOW(),
status = discount_campaign_advanced_status;
END IF;
END
the problem is in last part which in this part
SET
discount_advanced_rules_id = discount_campaign_advanced_discount_advanced_rules_id,
value = discount_campaign_advanced_value,
status = discount_campaign_advanced_status;
IF (discount_campaign_advanced_discount_advanced_rules_id = 0) THEN
INSERT INTO discount_campaign_advanced
SET
discount_campaign_id = last_id,
discount_advance_rules_id = discount_campaign_advanced_discount_advanced_rules_id,
value = discount_campaign_advanced_value,
createdAt = NOW(),
status = discount_campaign_advanced_status;
END IF;
END
when i run it, it's appear Error 1193: Unknown system variable 'discount_advanced_rules_id' SQL statement which part i missed?
this store procedure contain 3 tables which had connected, but on last part, the logic was like this, if discount_advanced_rules_id was 0, then the data must insert into discount_campaign_advanced table, otherwise, if not, then not insert to that table.
discount_advanced_rules_id
is not declared in your code.
You can declare it after the begin:
SET @discount_advanced_rules_id = 0;
and then you can use it:
SET
@discount_advanced_rules_id = discount_campaign_advanced_discount_advanced_rules_id,
value = discount_campaign_advanced_value,
status = discount_campaign_advanced_status;
The same work with every variable that doesn't comming from your input.
And you need to use @ before the name of the variable.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments