>I need to be able to return the result of a string that contains an expression. For example, given the string '(3+4)', I want the function to return the numeric result of 7. Does anyone know of a T-SQL function that will do this, or know how to write one?
>
>
>Thanks.
You'd have to use dynamic sql for this, which isn't nearly as clean as the good ol' EVAL().
Depending on the usage of the results, you might want different implementations.
e.g. - you could create a stored procedure which has an output parameter for the value to return.
create procedure up_eval @expr nvarchar(50), @retval numeric(15,3) output
as
declare @cmd1 nvarchar(4000)
set @cmd1='select '+@expr
create table #tmpret (retval int)
insert into #tmpret exec sp_executesql @command1=@cmd1
select @retval=retval from #tmpret
drop table #tmpret
notes on using sp_executesql (read more in BOL)
1. expects nvarchar parameters, if you pass a string directly, precede it with N - e.g.,
exec sp_executesql N'select (3+4)'
2. cannot concat values in the call itself, must have whole string or whole variable, e.g. this would not work
exec sp_executesql N'select '+@expr
3. cannot return a value directly, it only returns a result set, hence the temp table. also, must use temp table rather than table variable, since you cannot
insert into...exec sp..
using a table var.
calling it
declare @result numeric(15,3)
exec up_eval N'(3+4)', @result OUTPUT
Insanity: Doing the same thing over and over and expecting different results.