Evaluate user define formula (id, column) with SQL

Question

I have 2 tables in SQL Server, with

DECLARE @Formulas TABLE (ID int, Calculation varchar(max)) 

INSERT INTO @Formulas 
VALUES (1, '([445660][SOLDE]+[401000][DEBIT]-[380100][CREDIT])')

DECLARE @Values TABLE (ValueID int, Solde money, Debit money, Credit money)

INSERT INTO @Values 
VALUES (445660, 2500, 5500, 3000),
       (401000, 0, 2500, 0),
       (380100, 0, 0, 3500.24)

DECLARE @SQL varchar(max)=''

SELECT @SQL = @SQL+concat(',(',ID,',',Calculation,')') From @Formulas --Where ID=2

SELECT @SQL = Replace(@SQL,'['+cast(ValueID as varchar(25))+']',/[Value]/) From @Values

SELECT @SQL = 'Select * From ('+Stuff(@SQL,1,1,'values')+') N(ID,Value)'

EXEC(@SQL)

The /[Value]/ in the 3 code must change to column (Solde, Debit, Credit) because I have 3 columns, and the user can choose any of them.

I take a example Calculating/Evaluate user defined formula with SQL but it is with single column

Thanks in advance.

Answer 1

Perhaps this will help. ( thought that code looked familiar)

Here we use a CROSS APPLY to UNPIVOT the Values table creating a MapFrom and MapTo

Declare @Formulas table (ID int,Calculation varchar(max)) 
Insert Into @Formulas values
(1,'([445660][SOLDE]+[401000][DEBIT]-[380100][CREDIT])')

Declare @Values table (ValueID int,Solde money,Debit money,Credit money)
Insert Into @Values values
(445660,2500,5500,3000),
(401000,0,2500,0),
(380100,0,0,3500.24)

Declare @SQL varchar(max)=''
Select  @SQL = @SQL+concat(',(',ID,',',Calculation,')') From @Formulas --Where ID=2
Select  @SQL = Replace(@SQL,MapFrom,MapTo) 
  From  @Values A 
  Cross Apply (values ( concat('[',ValueID,'][SOLDE]' ),concat('',Solde))
                     ,( concat('[',ValueID,'][DEBIT]' ),concat('',Debit))
                     ,( concat('[',ValueID,'][CREDIT]'),concat('',Credit))
        ) B(MapFrom,MapTo)
Select  @SQL = 'Select * From ('+Stuff(@SQL,1,1,'values')+') N(ID,Value)'
Exec(@SQL)

Results

ID  Value
1   1499.76