Execute the following code, which creates the catalog_get_attributes_not_assigned_to_product
stored procedure in your tshirtshop database. This procedure returns all attribute values that weren??™t
already associated with the product in the product_attribute table.
-- Create catalog_get_attributes_not_assigned_to_product stored procedure
CREATE PROCEDURE catalog_get_attributes_not_assigned_to_product(
IN inProductId INT)
BEGIN
SELECT a.name AS attribute_name,
av.attribute_value_id, av.value AS attribute_value
FROM attribute_value av
INNER JOIN attribute a
ON av.attribute_id = a.attribute_id
WHERE av.attribute_value_id NOT IN
(SELECT attribute_value_id
FROM product_attribute
WHERE product_id = inProductId)
ORDER BY attribute_name, av.attribute_value_id;
END$$
12. Execute the following code, which creates the catalog_assign_attribute_value_to_product
stored procedure in your tshirtshop database. This procedure assigns an attribute value to a product by
adding a new record to the product_attribute table.
-- Create catalog_assign_attribute_value_to_product stored procedure
CREATE PROCEDURE catalog_assign_attribute_value_to_product(
IN inProductId INT, IN inAttributeValueId INT)
BEGIN
INSERT INTO product_attribute (product_id, attribute_value_id)
VALUES (inProductId, inAttributeValueId);
END$$
13. Execute the following code, which creates the catalog_remove_product_attribute_value stored
procedure in your tshirtshop database.
Pages:
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460