September 26, 2006 at 9:17 am
How can I evaluate a mathmatical function that is stored in a column of a table?
Here's a scenario:
Tbl1 : name varchar(20), fn varchar(15)
Tbl2 : val float
Tbl1 has 1 record : ('function #1','x / .65')
Tbl2 has 3 records : 3, 4, 5
I would like to return the following recordset:
name, fn, val, result
function #1,x / .65,3,4.615
function #1,x / .65,4,6.153
function #1,x / .65,5,7.692
I have been doing the calculation in server code just fine, however I need to narrow my results the calculated field (e.g. WHERE result < 6)
I'm thinking a udf would be good, but I cannot get one to work. The below works in query analyzer, but I can't put the exec into a udf:
declare
@math varchar(19)
set @math = '(1+2.3)/2'
declare @q varchar(50)
set @q = 'SELECT ' + @math
exec(@q)
September 26, 2006 at 10:35 am
You can try this
select
name, fn, val,cast (val/0.65 as decimal(9,3)) as result from Tbl1 cross join tbl2
where val/0.65 <6
--SQL 2005 Version
with
cte(name, fn, val,result) as (
select
name, fn, val,cast (val/0.65 as decimal(9,3)) as result from Tbl1 cross join tbl2
)
select
* from cte where result<6
September 26, 2006 at 11:02 am
The query you added does work, however you have taken the value of the fn column out of the database and directly into the sql statement. If the data in fn changes, you would have to go back to the sql statement and change that as well.
Specifically "val/0.65" is a string, not an expression.
This works: cast(3/0.65 as decimal(9,3))
This does not: cast('3/0.65' AS decimal(9,3))
Are there any other ideas out there?
September 26, 2006 at 4:09 pm
You probably need to read about sp_executesql in BOL.
It allows you to use parameters in dynamic string.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply