locked
using EXEC to execute a formula stored in a string RRS feed

  • Question

  • Hopefully someone will have run into this before...  Basically, I have a table with a column that stores mathematical formulas in string format.  When my UDF is executed, it needs to select an appropriate formula from this table and evaluate it using values that are stored in local variables.  Look at the example below:

    Suppose I have a string named @vcFormula that contains the following:
    "@dVar1 + @dVar2 / @dVar2"

    Now suppose I have a variable named @dVar1 that contains a value of 1.0, and variable @dVar2 contains a value of 2.5.  I can use the REPLACE function to change my original string to look like this:
    "1.0 + 2.5 / 2.5"

    Now I want to execute this string and find the numeric result, placing it in a variable named @dResult.  The following works, but presents a problem:

    CREATE TABLE #Result (dResult decimal(20, 10))
    INSERT #Result EXEC('SELECT ' + @vcFormula)
    SELECT @dResult = dResult FROM #Result

    The problem with using this method comes from the fact that I need to be able to evaluate @vcFormula from within a user-defined function, but temporary tables are not allowed inside UDF's.  So I attempted to change the temporary table above into an instance of the TABLE data type.  This didn't work either because EXEC cannot be used to populate instances of the TABLE data type.  Then I came up with the bright idea to put the code above in a SP and call the SP from the UDF, but of course UDF's are not allowed to call SP's.

    So I'm stuck - does anyone have any suggestions?  Specifically, is there any way to execute a command/formula that is contained within a string other than by using EXEC? 
    Tuesday, May 31, 2005 5:56 PM

Answers

  • Here is the answer to my own question:

    After I explained the difficulty I have had with this to my users, they decided that it wasn't something that had to be done.

    So now it is a problem for another day.  Thanks anyway, Clifford.
    Friday, June 10, 2005 6:56 PM

All replies

  • hello..

    i made test, these methods could be used...
    =================================
    declare @v1 nvarchar(10)
    declare @v2 nvarchar(10)
    declare @v3 nvarchar(10)
    set @v1 = '2.5'
    set @v2 = '4.5'
    set @v3= '5.5'

    declare @sql nvarchar(200)
    declare @param nvarchar(100)
    declare @resultOut nvarchar(100)

    -- Method 1:
    set @sql = N'select (' + @v1 + '+' + @v2 + '/' + @v3 + ')'
    exec sp_executesql @sql

    -- Method 2:
    set @sql = N'select @calc=(' + @v1 + '+' + @v2 + '/' + @v3 + ')'
    set @param = N'@calc nvarchar(30) output';

    exec sp_executesql @sql, @param, @calc=@resultOut output;
    select @resultOut
    =================================

    Friday, June 3, 2005 9:56 PM
  • Thanks for the reply, but you didn't actually address the real problem.

    The real problem is that I need to be able to call similar code from inside a UDF.  Please look at the original post to see the explanation of the problem.
    Monday, June 6, 2005 5:19 PM
  • Hi,

    You wrote "Then I came up with the bright idea to put the code above in a SP and call the SP from the UDF, but of course UDF's are not allowed to call SP's."

    Could you do this in a few passes?

    Pass 1 - UDF selects formula templates and creates parameterized formula instances.  Use this UDF as the rowset source to fill up a temp table.

    Pass 2 - Iterate over temp table using a cursor calling sp_execsql on each formula expression.  Compute result and store into result column.

    Pass 3 - Select results from temp table

    Unfortunately, I don't see a nicer way to do this.  I looked at using

    SELECT * FROM   OPENQUERY(LOOPBACK_SERVER, @vcFormula)

    but apparently OPENQUERY only accepts string literals for the 2nd parameter.

    Regards,
    Clifford Dibble



    • Proposed as answer by Naomi N Wednesday, July 1, 2015 4:50 PM
    Wednesday, June 8, 2005 2:45 AM
  • BTW, others reasons that dynamic SQL is not allowed inside functions include:

    i) We cannot tell whether the code would have side effects or not (side effects are not allowed in functions)

    ii) We cannot tell whether the code is deterministic or not (a function must be deterministic to be used in a computed column, an index, or an indexed view)

    Regards,
    Clifford Dibble
    • Proposed as answer by Naomi N Wednesday, July 1, 2015 4:50 PM
    Wednesday, June 8, 2005 6:27 PM
  • Here is the answer to my own question:

    After I explained the difficulty I have had with this to my users, they decided that it wasn't something that had to be done.

    So now it is a problem for another day.  Thanks anyway, Clifford.
    Friday, June 10, 2005 6:56 PM
  • OK.  Sad
     
    I'm truly sorry this was so painful for you.   As we begin to plan for the next post-Yukon release of SQL Server, maybe this is something we can address.  It would help to know as much about your usage scenario as possible (e.g., why you absolutely HAD to eval the formulata from inside a UDF).   As I spoke with your problem with some of my colleagues, we weren't entirely sure about all the details of your scenario. 

    For example, one idea that came up was just to dynamically create the entire function ("CREATE FUNCTION ...") and then drop it when you're done.  However, I argued that wouldn't work because I assumed you needed to eval one formula for each row in a result set.

    In any case, if you get some time, tell us about your scenario and restrictions and so on.

    Thanks,
    Clifford Dibble

    Friday, June 10, 2005 7:29 PM
  • Clifford,

    First off, thanks for being so eager to help.  I think you should be commended for your genuine desire to help out the folks on this board.  Secondly, I really appreciate Microsoft for setting up these forums - they have already been a great help to me, and I'm sure others as well.

    Basically, we have a huge legacy materials accounting program that was written in PowerBuilder.  I have been given the task to rewrite a major piece of this program in TSQL.  The piece in question is a globally defined function that is very complex.  Its purpose is to return several pieces of data about individual quantities of some of our materials, given a few parameters to specify a particular individual quantity.  Some of the returned data is simply mined from the database, the rest of it is calculated.  The TSQL function I have written returns a table that contains all the desired values.

    Sometimes this TSQL function will be called by what PowerBuilder calls "inline SQL", which is SQL that is compiled and executed like regular PowerBuilder function calls.  At other times, this function will be called as the basis of a "datawindow" (i.e. a structure fairly similar to an MFC CRecordSet).  Finally, we wanted our users to be able to call the function and join to the table that it returns, in a larger query.  Although this final restriction seems difficult right now, I refer to your answer in another thread about how this may be done in the future using CROSS APPLY.  It is because of these reasons that a SP seemed to not be the correct choice for our needs, although in hindsight it now seems like one may have worked at least as well as a UDF.

    You are correct in your assumption that each row in the set may have a different formula to evaluate.

    If you have any specific questions, please feel free to ask.

    Dean
    Monday, June 13, 2005 3:15 PM
  • Hello

    I faced the same requirement few months back and found the solution, you can find in the follwoing link

    tell me if it's Ok for you and if you find usefull:

    http://www.dotnetspark.com/kb/5037-how-to-evaluate-formula-using-sql-server.aspx

    KEMAL AL GAZZAH

    Thursday, July 26, 2012 1:32 PM
  • Hi,

    I realize this post is a decade old but if there are any updates around storing a formula, pulling it out, inserting the values and executing it I'd love to know about them. As to your response; the level of support I have seen from Microsoft for line developers is STELLAR. I love being an MS dev. Heck I even got a reply once to an email I sent to Scott Gutherie.

    Thanks much,

    Bill

    Wednesday, July 1, 2015 3:29 PM
  • hello

    i have the same problem.

    i have unit of measure conversion formula stored in a table.

    i need to run a report using SSRS which needs to calculate values using these formula.

    i have create a function which accepts @Value and @FormulaID and built dynamic SQL to be executed using sp_executesql. This can't be done within function.

    I then created a stored procedure to do the same thing with an output variable, but cant call this in my main stored procedure which is used in the report.

    what should i do?

    thanks

    graham

    Thursday, November 5, 2015 12:04 PM