"Evgeny Gopengauz" <ev***@ucs.ru> wrote in message
news:41**********************@news.newsgroups.ws.. .
For example, I have a table FORMULA_TABLE with the column FORMULA which
contains some function y=f(x) in its symbol description like '@X*2+1'
create table FORMULA_TABLE(
ID int,
FORMULA varchar(100)
)
This is a sample how I'm going to calculate the function f(x) by its
number @ID:
declare @FORMULA varchar(100)
select @FORMULA = FORMULA
from FORMULA_TABLE
where F.ID=@ID
set @FORMULA =
'declare @X int '
+' declare @Y int '
+' set @X='+convert(varchar(10),x)
+' set @Y='+@FORMULA
exec(@FORMULA)
-- some manipulations with @Y expected to be there
How to retrieve back the value of @Y to the T-SQL context?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
You can use sp_executesql with an output parameter:
declare @FORMULA nvarchar(100)
declare @sql nvarchar(100)
declare @x int, @y int
set @FORMULA = '@X*2+1'
set @sql = 'select @y = ' + @formula
exec sp_executesql @sql, N'@x int, @y int OUTPUT', @x = 1, @y = @y OUTPUT
select @y
This assumes that all your formulae return an integer, of course. A more
general solution would be difficult, since you don't know in advance what
the data type of the return value will be, but in a limited case it should
work OK. If you have a small number of formulae, it might be worth
converting them to UDFs, although scalar UDFs perform poorly on large data
sets, so this might not be an option anyway.
See here for more details on output parameters:
http://support.microsoft.com/default...;EN-US;q262499 http://www.sommarskog.se/dynamic_sql.html#sp_executesql
Simon
