Dynamic calculation

Hi,
I get different values and a calculation from a query and need to bring them
all together to make another calculation.
@.PRICE,@.TIME,@.CALC,@.TOTAL
@.PRICE = 3
@.TIME = 5
@.CALC = '* .5'
@.TOTAL = (@.PRICE @.CALC) * @.TIME
I tried to use the exec command, but couldn't get it to work
How can I do this using Transact SQL?
Thank you,
MosheProbably you can make use of CASE expressions to do this, assuming you
know something about the types of optional calculations. Example:
SET @.total =
CASE @.calc_type
WHEN 1 THEN @.price*@.time*0.5
WHEN 2 THEN @.foo*@.bar*0.25
WHEN ... etc
END
If you think you will be forced to use dynamic code then there should
be no reason why you can't do it with EXEC. That's not necessarily the
approach I would recommend but maybe if you post some actual code
rather than pseudo code we could help you fix it.
David Portas
SQL Server MVP
--|||Seems to me you're trying to use dynamic SQL - don't quite know why, since
what you need can be done much more efficiently without dynamic SQL, but
still...
Read more here:
http://www.sommarskog.se/dynamic_sql.html
For a more efficient solution, please provide more information.
ML|||SET @.TOTAL = (@.PRICE @.CALC) * @.TIME
HTH, jens Suessmeyer.|||Hi Moshe,
I've done something very similar with a financial research app i wrote for a
client, they specify a couple of hundred dynamic formula that i then need to
calculate on the fly.
Basically you can use sp_executesql and get the output...
declare @.nsql nvarchar(4000)
set and declare... @.PRICE,@.TIME,@.CALC,@.TOTAL
SET @.PRICE = 3
SET @.TIME = 5
SET @.CALC = '* .5'
SET @.nsql = '@.TOTAL = (@.PRICE ' + @.CALC + ') * @.TIME'
EXEC sp_executeSQL @.nsql,
N'@.PRICE int, @.TIME int, @.TOTAL
decimal( 10, 2 ) OUTPUT',
@.PRICE, @.TIME, @.TOTAL OUTPUT
PRINT @.TOTAL
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Moshe Allen" <mosheallen@.hotmail.com> wrote in message
news:dmerbr$hba$1@.news2.netvision.net.il...
> Hi,
> I get different values and a calculation from a query and need to bring
> them all together to make another calculation.
> @.PRICE,@.TIME,@.CALC,@.TOTAL
> @.PRICE = 3
> @.TIME = 5
> @.CALC = '* .5'
> @.TOTAL = (@.PRICE @.CALC) * @.TIME
> I tried to use the exec command, but couldn't get it to work
> How can I do this using Transact SQL?
> Thank you,
> Moshe
>