May 8, 2017 at 11:40 am
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!
May 8, 2017 at 12:00 pm
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
May 8, 2017 at 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.
May 8, 2017 at 1:27 pm
rwr901012 - Monday, May 8, 2017 1:12 PMThanks, 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
May 9, 2017 at 7:38 am
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)
May 9, 2017 at 9:00 am
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".
May 10, 2017 at 6:12 am
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