salix/db/routines/vn/functions/item_getVolume.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` FUNCTION `vn`.`item_getVolume`(vSelf INT, vPackaging VARCHAR(10))
	RETURNS int(11)
	DETERMINISTIC
BEGIN
/**
 * Calculates the volume occupied by an item together
 * with its packaging.
 *
 * @param vSelf The item id
 * @param vPackaging The packaging id
 * @return The volume in cubic centimeters
 */
	DECLARE vVolume INT;

	SELECT SUM(IF(p.volume > 0,
			p.volume,
			p.width * p.depth * IF(IFNULL(p.height,0), p.height, IFNULL(i.size,60) + 10)
		)) INTO vVolume
		FROM packaging p
			JOIN item i ON i.id = vSelf
		WHERE p.id = vPackaging;
		
	RETURN vVolume;

END$$
DELIMITER ;