NateTheGreat NateTheGreat - 21 days ago 6
SQL Question

SQL Evaluate formula stored in database

Is it possible to evaluate a string formula/expression stored in a column? (SQL Server 2014).

Example:

TABLE:
ID | Formula
1 | IIF(2<3,'A','B')
2 | IIF(3<4,'C','D')
3 | IIF(5<1,'E','F')


Query:

SELECT ID, Eval(Formula)


Output:

1 | A
2 | C
3 | F

Answer

With Dynamic SQL, but REALLY not recommended. Just in case you REALLY have to...

Declare @YourTable table (ID int,Formula varchar(100))
Insert Into @YourTable values
(1,'IIF(2<3,''A'',''B'')'),
(2,'IIF(3<4,''C'',''D'')'),
(3,'IIF(5<1,''E'',''F'')')

Declare @SQL varchar(max) = '>>>'

Select @SQL = Replace(@SQL + concat(' Union All Select ID=',ID,',Value=',Formula),'>>> Union All','')
 From  @YourTable

Exec (@SQL)

Returns

ID  Value
1   A
2   C
3   F
Comments