Dynamic SQL - Run formula in field

  • I have a field in my table that consists of formulas.....the formulas are tied to field name from a different table. How can I run the formula using dynamic sql?

    Ex.
    Table 1:
    ----------------------------------------------
    Key | formula
    A1   | (colA * col B) + colC
    A2   | (colA / colB ) * colD)

    Table 2
    key | ColA | ColB | Col C| ColD
    A1  |   1     |   2    |    3    |    4
    A2  |    4    |     3  |    2    |     1

    A1 = 5
    A2 = .75
    Tried to make this as basic as possible for the example. Any guidance would be great!

  • You need to store mutliple associated pieces of data: table that has the field, field, operator/code. Then you need code that will reconstruct valid TSQL from this at run-time. This is tedious but not rocket science.

    PRINT @sql will be your friend while developing this. 🙂

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Thanks, do you have any examples of this already done? The formulas vary so I am trying to determine how to break it apart as well as store the operator and fields to be used again at run-time.

  • rwr901012 - Monday, May 8, 2017 1:12 PM

    Thanks, do you have any examples of this already done? The formulas vary so I am trying to determine how to break it apart as well as store the operator and fields to be used again at run-time.

    Nope - sure don't. I've seen more things than you can imagine in SQL Server. But this is a very specific need. From what little bit I know I question the mechanism being used. Sadly it is too complex a problem space for a forum solution though, at least for me.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • As formulas require a fairly complex parsing methodology in order to allow much in the way of functionality, providing a solution here in a forum is just not a realistic expectation.   I also question the viability of such a method, unless there are extraordinarily strict limits on what is allowed in those formulas and the overall complexity thereof is extremely simple.   It wouldn't take much in complexity to break a parser that otherwise works well on simpler computations.   Just ensuring the correct order of operations is a surprisingly complex task.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • For now, off the top of my head, I don't see any way to avoid a cursor. so that you can dynamically evaluate each expression using sp_executesql.  But other than that, it's not too bad a process, something like this:


    DECLARE @ColA decimal(19, 2)
    DECLARE @ColB decimal(19, 2)
    DECLARE @ColC decimal(19, 2)
    DECLARE @ColD decimal(19, 2)
    DECLARE @formula varchar(200)
    DECLARE @key varchar(10)
    DECLARE @sql nvarchar(4000)
    DECLARE @value decimal(19, 2)

    IF OBJECT_ID('tempdb.dbo.#results') IS NOT NULL
      DROP TABLE #results

    CREATE TABLE #results (
      varchar(10) NOT NULL,
      value decimal(19, 2) NULL,
      formula varchar(200) NOT NULL
      )

    --SELECT * FROM #table1
    DECLARE cursor_table2 CURSOR FAST_FORWARD FOR
    SELECT t2., t1.formula, t2.ColA, t2.ColB, t2.ColC, t2.ColD
    FROM #table2 t2
    INNER JOIN #Table1 t1 ON t1. = t2.
    ORDER BY t2.

    OPEN cursor_table2

    WHILE 1 = 1
    BEGIN
      FETCH NEXT FROM cursor_table2 INTO @key, @formula, @ColA, @ColB, @ColC, @ColD
      IF @@FETCH_STATUS <> 0
       IF @@FETCH_STATUS = -1
        BREAK
       ELSE
        CONTINUE;
      SET @sql = 'SELECT @value = ((1.0)) * ' +
      REPLACE(REPLACE(REPLACE(REPLACE(@formula,
       'colA', CAST(@colA AS varchar(30))),
       'colB', CAST(@colB AS varchar(30))),
       'colC', CAST(@colC AS varchar(30))),
       'colD', CAST(@colD AS varchar(30)))
      --PRINT @sql
      EXEC sp_executesql @sql, N'@value decimal(19, 2) OUTPUT', @value OUTPUT
      INSERT INTO #results ( , value, formula)
      SELECT @key, @value, @formula
    END /*WHILE*/

    DEALLOCATE cursor_table2

    SELECT * FROM #results ORDER BY

    /*
    IF OBJECT_ID('tempdb.dbo.#Table1') IS NOT NULL DROP TABLE #Table1
    CREATE TABLE #Table1 (
      varchar(10) NOT NULL PRIMARY KEY,
      formula varchar(1000) NOT NULL
      )
    INSERT INTO #Table1
    VALUES('A1', '(colA * colB) + colC'),('A2', '(colA / colB ) * colD')

    IF OBJECT_ID('tempdb.dbo.#Table2') IS NOT NULL DROP TABLE #Table2
    CREATE TABLE #Table2 (
      varchar(10) NOT NULL,
      ColA decimal(19, 2) NULL,
      ColB decimal(19, 2) NULL,
      ColC decimal(19, 2) NULL,
      ColD decimal(19, 2) NULL
      )
    INSERT INTO #Table2
    VALUES('A1' , 1 , 2 , 3 , 4),('A2' , 4 , 3 , 2 , 1)
    */

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Make a dynamic script which create functions named, say, {Table 1}_Formula_{Key} based on the script saved against each key.
    Put it into a job with no schedule.
    Test it by running manually, make sure it handles bad entries in the table appropriately.
    Once you're confident it works fine create a trigger on Table 1 which would start the job every time there is a change in Table 1.

    Use those functions in Table 2.

    _____________
    Code for TallyGenerator

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply