(SOLVED)Stored Procedure that will replaced and compute Dynamic Formula saved in a column

Transact-SQL
2 
-- create table defs and populate data
CREATE TABLE Table_Formula(id int not null PRIMARY KEY, country varchar(20) not null, Formula varchar(100) not null);

INSERT Table_Formula VALUES 
(1,'Germany','[AMOUNT]*(1+[FEE])*(1+[MAIN])')
,(2,'India','[AMOUNT]*(1+[MAIN])')
,(3,'Korea','[AMOUNT]*(1+[FEE])*(1+[SUB])');

CREATE TABLE Table_Rate(id int not null PRIMARY KEY, country varchar(20) not null
, Category_1 varchar(10) not null,	Value_1 money not null
, Category_2 varchar(10) null,	Value_2 money null
, Category_3 varchar(10) null,	Value_3 money null);

INSERT Table_Rate VALUES
(1,'Germany','AMOUNT',600,'FEE',50,'MAIN',70)
,(2,'India','AMOUNT',700,'MAIN',60,null,null)
,(3,'Korea','AMOUNT',500,'FEE',40,'SUB',99);

DECLARE @sql nvarchar(max)=N'';
-- unpivot category and value columns into 2 normalized columns
-- re-pivot categories and their values into column names
-- apply formula using generated column names
-- construct SQL with these statements to calculate formula
SELECT @sql+=FORMATMESSAGE(N'%s SELECT id, country, %s computed_value FROM cte2 WHERE id=%d ',
	CASE WHEN id=1 
	THEN N'WITH cte AS(SELECT a.id,a.country, cats.* FROM Table_Rate a
CROSS APPLY(VALUES(Category_1,Value_1), (Category_2,Value_2), (Category_3,Value_3)) AS cats(cat,val)
WHERE cats.cat IS NOT NULL)
,cte2 as (SELECT id, country, [AMOUNT],[FEE],[MAIN],[SUB]
FROM (SELECT id, country, cat, val FROM cte) a
PIVOT(MAX(val) FOR cat IN ([AMOUNT],[FEE],[MAIN],[SUB])) b)
' 
	ELSE N'UNION ALL' END,Formula,id)
FROM Table_Formula
ORDER BY id
-- execute generated SQL
EXEC(@sql);

This doesn't include the Formula column or the replacement values in the expression, it just calculates the formula.

1 Like