"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