June 23, 2009 at 12:19 pm
My first post, so I apologize in advance for any rules/etiquette that I'm breaking. Using sql2005 and wondering if there is any way to make this set based outside of writing a CLR function. The actual code needs to eventually be a stored procedure that takes a database as a parameter and processes thousands or rows, but I can worry about that later. Thanks for any help.
create table #formulaTable(formula varchar(100), amount decimal(9, 2))
declare @formula varchar(100)
declare @sql nvarchar(max)
declare @result decimal(9,2)
insert into #formulaTable
(formula)
select '3 * (2 + 4)'
insert into #formulaTable
(formula)
select '7 + 2'
declare evalCursor cursor for
select formula
from #formulaTable
open evalCursor
fetch next from evalCursor into @formula
while @@FETCH_STATUS = 0
begin
set @sql = N'set @result = ' + @formula
set @sql = @sql + N'update #formulaTable set amount = @result where current of evalCursor'
exec sp_executesql @sql, N'@result decimal(9,2)',0
fetch next from evalCursor into @formula
end
close evalCursor
deallocate evalCursor
select *
from #formulaTable
drop table #formulaTable
June 23, 2009 at 3:30 pm
I fear you have no other option than using either CLR or dynamic SQL.
Of course you could do somthing like the following in a T-SQL function
The expression evaluator revisited (Eval function in 100% managed .NET)
but It will be slow and also way too complicated to write.
Best Regards,
Chris Büttner
June 23, 2009 at 3:32 pm
I don't know about too complicated....
drop table #formulaTableshort
drop table #formulaTable
create table #formulaTableshort(formula varchar(100))
declare @formula varchar(100)
declare @sql nvarchar(max)
declare @result decimal(9,2)
--test data
insert into #formulaTableshort
(formula)
select '3 * (2 + 4)'
insert into #formulaTableshort
(formula)
select '7 + 2'
--now for the real fun
create table #formulaTable(formula varchar(100), amount decimal(9, 2))
set @sql='insert #formulaTable(formula,amount)'
select @sql=@SQL+'SELECT '''+formula+''','+formula+' UNION ALL '
from #formulaTableshort
select @sql=left(@SQL,len(@SQL)-10)
print @sql
exec(@SQL)
select * from #formulatable
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 23, 2009 at 3:55 pm
Thanks for the replies! I'm guessing a CLR will be the best performer, but my current dataset isn't big enough to need it yet. But I may feel the need to experiment....
Matt, your solution is ingenious. Too complicated for me to understand at first glance. I will put it to use and see how it matches up against the mighty cursor.
Thank you both!
June 24, 2009 at 12:26 am
Matt Miller (6/23/2009)
I don't know about too complicated....
Just for clarification - I wrote about 3 solutions.
The only one that I said would be complicated is the last one where you create your own math parsing and evaluation T-SQL function (without dynamic SQL or CLR).
Best Regards,
Chris Büttner
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply