Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8618

Fr: DYNAMIC SQL into functions

        Details

        • Type: Task
        • Status: Open
        • Priority: Minor
        • Resolution: Unresolved
        • Fix Version/s: None
        • Component/s: Prepared Statements
        • Labels:
          None

          Description

          Will someday possible to use the DYNAMIC SQL into functions? Or MariaDB has fundamental architectural constraints for this? If possible, then this feature request that such feature came soon as possible.

          I would be much happier if can instead of this procedure:

          CREATE PROCEDURE `appl_loan_rate_calc`(
IN par_id_tariff INT(10) UNSIGNED
, IN par_loan_sum DECIMAL(12,2)
, OUT par_rate DECIMAL(10,2)
)
BEGIN  
	DECLARE var_formula VARCHAR(100);
	SELECT rate INTO var_formula FROM `appl_loan_tariff` WHERE id_tariff = par_id_tariff;
	SET @loan_sum = par_loan_sum;
	SET @sql_statement = CONCAT('SET @rate = ',var_formula);

	PREPARE select_sql FROM @sql_statement;
	EXECUTE select_sql;
	DEALLOCATE PREPARE select_sql;

	SET par_rate = @rate;
END

          write function:

          CREATE FUNCTION `appl_loan_rate_calc`(
par_id_tariff INT(10) UNSIGNED
, par_loan_sum DECIMAL(12,2)
) RETURNS DECIMAL(10,2)
    READS SQL DATA
    DETERMINISTIC
BEGIN
	DECLARE var_formula VARCHAR(100);
	SELECT firmula INTO var_formula FROM `appl_loan_tariff` WHERE id_tariff = par_id_tariff;
	
	PREPARE dymanic_sql FROM CONCAT('SET var_rate = ',var_formula);
	EXECUTE dymanic_sql;
	DEALLOCATE PREPARE dymanic_sql;

	RETURN var_rate;
END

          Because i need calculate for several tariffs which appropriate to conditions, of course i can do it with cursor and temporary table, but solution with function I like more.

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

                  • Assignee:
                    Unassigned
                    Reporter:
                    mikhail Mikhail Gavrilov
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    1 Start watching this issue

                    Dates

                    • Created:
                      Updated: