SQL Server: How to select results of a formula stored as a string.

Question

I have a table that has 3 columns: PlacementID, EarnCode, and Formula. As an example, let's say it has 3 rows.

PlacementID EarnCode Formula
1111 HOL (50 x 2) + 10 + 5
1111 REG 50 x 2
2222 HOL 100 x 1.5

Is there any way to simply select the results of the formula? Ideally my results would be:

PlacementID EarnCode RESULTS
1111 HOL 115
1111 REG 100
2222 HOL 150

Answer 1

Before giving new details, try simplifying this script too:

declare @mytable table( PlacementID int, EarnCode varchar(max), Formula varchar(max))

insert @mytable values
( 1111, 'HOL', '(50 x 2) + 10 + 5' ),
( 1111, 'REG', '50 x 2           ' ),
( 2222, 'HOL', '100 x 1.5        ' )

declare c cursor for select PlacementID, EarnCode, Formula from @mytable
declare @id int
declare @ec varchar(max)
declare @f varchar(max)
declare @result table( PlacementID int, EarnCode varchar(max), RESULTS float)

open c

while 0 = 0
begin
    fetch next from c into @id, @ec, @f

    if @@FETCH_STATUS <> 0 break

    declare @s nvarchar(max)
    set @s = concat('select @r = ', replace(@f, 'x', '*'))

    declare @r float = null

    exec sp_executesql @s, N'@r float output', @r output

    insert @result select @id, @ec, @r

end

close c deallocate c

select * from @result    

Answer 2

Hi @Ann Russell ,

Welcome to the microsoft tsql Q&A forum!

Please also check:

CREATE TABLE #test(PlacementID INT,EarnCode VARCHAR(MAX),Formula VARCHAR(MAX))  
INSERT INTO #test VALUES  
(1111,'HOL','(50 x 2) + 10 + 5'),  
(1111,'REG','50 x 2'),  
(2222,'HOL','100 x 1.5');  
  
UPDATE #test  
SET Formula=REPLACE(Formula,'x','*' )  
FROM #test;  
  
ALTER TABLE #test ADD Results NUMERIC(18,3);  
  
DECLARE @zd varchar(200),@sql varchar(200)  
DECLARE test CURSOR FOR  
SELECT Formula FROM #test  
OPEN test  
FETCH NEXT FROM test into @zd  
WHILE @@FETCH_STATUS = 0  
BEGIN  
SET @sql='update #test set Results = (select '+(select rtrim(Formula) from #test where Formula=@zd)+')'+' where Formula='''+rtrim(@zd)+''''  
EXEC (@sql)  
FETCH NEXT FROM test INTO @zd  
END  
CLOSE test  
DEALLOCATE test;  
  
SELECT * FROM #test  

Output:

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".