Can this cursor be made into a procedure?

  • 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

  • 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

  • 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?

  • 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!

  • 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