CODEWITHSUNDEEP
sqlsql-serveruser-defined-functions
RizJa
RizJa

Reputation: 2031

SQL Server - How to perform a calculation based on a dynamic formula

I have a case where I need to perform a dynamic calculation based on a particular entry in a column.

The table looks something like:

DECLARE Temp TABLE (
        Id INT, 
        Name NVARCHAR(255),
        Const1 DECIMAL(18,10),
        Const2 DECIMAL(18,10),
        Const3 DECIMAL(18,10),
        Const4 DECIMAL(18,10)
    );

I want to add in a field called "Calculation". The user has to specify in that field how the constants are to be applied (i.e. "Const1 * Const2 + (Const3 - Const4)").

I've got a function that has the formula hard coded but I want to be able to dynamically map the table columns to the "Calculation" field. Is this possible? If I'm getting a table entry like:

ID| Name         | Const1 | Const2 | Const3 | Const4 | Calculation
1 | Calculation1 |      5 |      3 |      2 |      9 | Const1 * Const2 + (Const3 - Const4)

Then in my function, can I dynamically make the calculation and return that as an output? Am I approaching this problem the correct way?

Thanks in advance!

Upvotes: 2

Views: 2323

Answers (1)

Tom H
Tom H

Reputation: 47444

You'll need to use dynamic SQL with sp_executesql or EXEC. I don't recall if those can be used in a UDF or not and there are some issues to be aware of, such as SQL Injection and possible performance issues. I don't have time to test whether or not this works in a UDT, but you would want something like:

DECLARE @sql VARCHAR(MAX)

SELECT @sql = 'SELECT ' + REPLACE(REPLACE(Calculation, 'Const1', Const1), 'Const2', Const2)... + ' AS result'
FROM My_Table
WHERE ID = 1

EXEC(@sql)

Upvotes: 5

Related Questions