469,081 Members | 1,808 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,081 developers. It's quick & easy.

How to get back the value from exec() ?

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!
Jul 20 '05 #1
1 9911

"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
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by PeterF | last post: by
2 posts views Thread by mirza i | last post: by
5 posts views Thread by =?Utf-8?B?RkxEYXZlTQ==?= | last post: by
2 posts views Thread by Mick Walker | last post: by
reply views Thread by flyingchen | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.