How to get math result from varchar formula?

Question

Post reply

How to get math result from varchar formula?

DavidCl

Mr or Mrs. 500

Points: 588
More actions
July 15, 2004 at 9:23 pm

#108565

I have a table with a varchar field that stores a formula used to calculate the list price of of products based on vendor etc. The cost of the product is to be run against the formula to arrive at the list price. The problem is how to run the math and get the result with out creating a small calculator program to do it. The code below demonstrates the problem.
declare @Cost Money
declare @Formula varchar(100)
select @Cost = 12.50
select @Formula = '$ + 12 * 3'
select @Formula = replace(@Formula, '$', convert(varchar(12), @Cost))
Is there a way that I can execute @Formula to get the result? I have tried execute and sqlexecute. But I have not found any syntax that works.
Thank you for your time. I will appreciate any suggestions.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Add after last of your statements:

select @Formula = 'select ' + @Formula -- or 'print '

execute (@Formula)

and run.

That's all.

_____________
Code for TallyGenerator

DavidCl Mr or Mrs. 500 Points: 588 More actions · Answer 2

Thank You! I thought it was something faily simple. I just could not seem to get it.

I have another question if you don't mind. Now, I need to get the result into a variable. I have gotten close but the exact syntax I have not figured out yet. Here is what I have been trying:

execute @Price = (@Formula)

But this returns an error. What am I missing?

Thanks again.

Derrick Leggett SSCrazy Points: 2778 More actions · Answer 3

declare @Cost Money

declare @Formula varchar(100)

select @Cost = 12.50

select @Formula = '$ + 12 * 3'

select @Formula = replace(@Formula, '$', 'cast(' + convert(varchar(12), @Cost) + ' as int)')

select @Formula = 'select ' + @Formula

--print @Formula

EXEC(@Formula)

Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.

Sergiy SSC Guru Points: 110208 More actions · Answer 4

You cannot use that syntax because @Formula is not SP.

Actually options for EXECUTE ('String') are quite limited. It exists in own environment, no variables to be transferred.

The only way I know is to use temp table. Something like this:

declare @Cost Money, @Price money

declare @Formula varchar(100)

Create table tmp_R (Result money)

select @Cost = 12.50

select @Formula = '$ + 12 * 3'

select @Formula = replace(@Formula, '$', convert(varchar(12), @Cost))

select @Formula = 'select ' + @Formula -- or 'print '

insert into tmp_R

execute (@Formula)

select @Price = result from tmp_R

Drop table tmp_R

But the better way - NOT TO USE DYNAMIC SQL! There are a dozen of ways to implement your sample task without executing string. I believe at least one of them is suitable for your real task. SPs, tables and views with calculated columns - all are ready for service.

Good luck,

Sergiy.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 5

You cannot use that syntax because @Formula is not SP.

Actually options for EXECUTE ('String') are quite limited. It exists in own environment, no variables to be transferred.

The only way I know is to use temp table. Something like this:

declare @Cost Money, @Price money

declare @Formula varchar(100)

Create table tmp_R (Result money)

select @Cost = 12.50

select @Formula = '$ + 12 * 3'

select @Formula = replace(@Formula, '$', convert(varchar(12), @Cost))

select @Formula = 'select ' + @Formula

insert into tmp_R

execute (@Formula)

select @Price = result from tmp_R

Drop table tmp_R

But the better way - NOT TO USE DYNAMIC SQL! There are a dozen of ways to implement your sample task without executing string. I believe at least one of them is suitable for your real task. SPs, tables and views with calculated columns - all are ready for service.

Good luck,

Sergiy.

_____________
Code for TallyGenerator

Derrick Leggett SSCrazy Points: 2778 More actions · Answer 6

????? The example I gave does work. Just run it. I agree that dynamic SQL isn't the best way to do this. It would be bettre to just create a fucntion that accepts an input parameter and returns the formula result. The example I showed definitely works though.

Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.

DavidCl Mr or Mrs. 500 Points: 588 More actions · Answer 7

That works Sergiy. I do appreciate everyone’s help!