Dynamic equations in sprocs? Using equations stored in a table.

Question

Dynamic equations in sprocs? Using equations stored in a table.

Jon-343748

SSChasing Mays

Points: 626
More actions
July 24, 2007 at 3:32 pm

#153705

Is there a way I can store an equation in a table, and then retrieve that equation at run time, so the mathmatical function is dynamic?
I have objects which need calculations done on them, but each object utilizes a different equation to do this calculation. Hardcoding this set of calcs for all of the objects would be a real hassle.
For example, object 1 uses this equation to generate revenue:
2X^2 + 5.6x + 5874.8
Where as object 2 uses:
4.5x^3 + 5x^2 + 3.4x + 547.5
Is there a way I can hold those equations in a varchar column, retrieve them at run time, and plug them into a stored procedure? I hate hardcoding a ton of items that I would much rather run as a loop or similar operation.
Thanks in advance.

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

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

bledu SSChampion Points: 12335 More actions · Answer 1

yes you can use dynamic sql, storing your string in a column as you have said. you would need to use the replace function. this is assuming that you have pretty generic formulae like pythagoras theorem or one of those.

But why do you want to do your equations in sql??? why not do it in the front end application, it makes more sense to me

Everything you can imagine is real.

Lowell SSC Guru Points: 323518 More actions · Answer 2

even better, why not just add a calculated field to the table to calculate this formula, isntead of trying to dynamically use a formula:

ALTER TABLE WHATEVER ADD PROJECTEDREVENUE AS 2 * (ISNULL(SOMECOLUMN,0.00)) ^2 + 5.6 * (ISNULL(SOMECOLUMN,0.00)) + 5874.80

2X^2 + 5.6x + 5874.8

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bledu SSChampion Points: 12335 More actions · Answer 3

which ever is better, you can also consider using a user defined function

Everything you can imagine is real.

Sergiy SSC Guru Points: 110208 More actions · Answer 4

... created by a trigger on that table.

You need only to have column with unique index on it for function names.

This will allow you to see if your internal formula interpreter can read and execute the formula you entered immediately, not at run time when you need to get results, not to struggle with debugging the formula.

_____________
Code for TallyGenerator

Jeff Gray SSChampion Points: 10667 More actions · Answer 5

Hi Jon,

I wrote a tiny FORTH interpreter (In T-SQL) to do something along the lines of what you are trying to do. It was either that or maintain 15,000 trivial sprocs.