March 24, 2006 at 2:59 pm
create table tmp (formula varchar(50), Result decimal(9,2) )
insert into tmp
(formula)
select '10.75- (.28*5) + 1.5000'
Is there any type of update statement that I can execute to get the result of that formula into the result column?
Thanks for any help!
- Steve
March 24, 2006 at 3:41 pm
You would need to do it as part of the insert. I don't know of a way for an update statement to work as SQL Server will have difficulty converting the string to decimal.
create table tmp (formula varchar(50), Result decimal(9,2) )
declare @formula varchar(50),
@result decimal
SELECT @formula = '10.75- (.28*5) + 1.5000', @result = (10.75- (.28*5) + 1.5000)
insert into tmp
(formula, result)
Values (@formula, @result)
March 24, 2006 at 4:07 pm
Thanks for the reply. Unfortunately I don't really know the formula until after the insert statement. There is a bit of a mess parsing out the variables just to get the formula. I'll play around with the execute statement and see if I can make any progress. Thanks for your help.
Steve
March 24, 2006 at 4:28 pm
This works, but you will need to add a where clause into the update statement and change the decimal scale and precesion to fit your needs. Also, if you need it to process multiple rows, you will need to change it accordinly.
declare @formula varchar(50),
@cmd nvarchar(4000)
select @formula = formula from tmp
set @cmd = N'set @result = ' + @formula
set @cmd = @cmd + N'update tmp set result = @result'
exec sp_executesql @cmd, N'@result decimal(10,2)',0
March 27, 2006 at 3:40 pm
Thanks for your help. That will work great. I'll probably need to use a cursor, but it's not that much data so I can live with it.
- Steve
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply