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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` FUNCTION `vn`.`client_getDebt`(`vClient` INT, `vDate` DATE)
	RETURNS decimal(10,2)
	NOT DETERMINISTIC
	READS SQL DATA
BEGIN
/**
 * Returns the risk of a customer.
 *
 * @param vClient client id
 * @param vDate date to check the risk
 * @return Client risk
 */
	DECLARE vDebt DECIMAL(10,2);
    DECLARE vHasDebt BOOLEAN;

	SELECT COUNT(*) INTO vHasDebt
		FROM `client` c
		WHERE c.id = vClient AND c.typeFk = 'normal';
	
	IF NOT vHasDebt THEN
		RETURN 0;
	END IF;

	CREATE OR REPLACE TEMPORARY TABLE tmp.clientGetDebt (clientFk INT);
	INSERT INTO tmp.clientGetDebt SET clientFk = vClient;

	CALL vn.client_getDebt(vDate);

	SELECT risk INTO vDebt FROM tmp.risk;

	DROP TEMPORARY TABLE
		tmp.clientGetDebt,
		tmp.risk;

	RETURN vDebt;
END$$
DELIMITER ;