Dynamic SQL - sp_executesql

Question

Dynamic SQL - sp_executesql

georg.schumann

Old Hand

Points: 320
More actions
December 16, 2009 at 1:07 am

#385953

Hi SQL - experts,
I have twisted my brain around but could not find an elegant solution to this problem.
I have 2 table: mathematical formulas and Operands (of these formulas) connected via FK on formula_id
I want to implement a generic way to evaluate each formula in the formulas table. The formulas are represented as varchar e.g. Op1 + Op2. The Operands are specified by a SELECT statement.
So what I am palnning to do is:
1) Select a formula and all its Operands
2) Execute the Select-Statement of each Operand to get their value
3) Insert this values in the formula and retrieve a result
Any suggestions please 😉
Cheers,
Georg

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Can you post the table definitions and some test data as outlined in the first article linked in my signature?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

georg.schumann Old Hand Points: 320 More actions · Answer 2

Hey Jack,

thanks for the reply. The table defibnitions are:

Table "Formula":

- Formula_ID (PK) e.g. 1

- Formula varchar e.g. Op1 + Op2

- PrecisionOfResult e.g. 2

Table "Operands": Both Operands belong to formula 1

- Operand_ID (PK) e.g. 1

e.g. 2

- Select_Stmt (this is where i find each Operand in DB) e.g. "Select Operand From table A" --> resulting value 3

e.g. "Select Operand From table B" --> resulting value 4

- Forumla_ID (FK) e.g. 1

e.g. 1

So in the Formula Table mathemactical Formulas are stored. In the Operands table are the Operands of each forumla defined. What I wanna do now is:

Catch a formula (Op1 + Op2) --> query its Operands (1, 2) --> evaluate Formula (3 + 4 = 7)

I hope this makes it clearer.

Cheer,

Georg

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Actually that isn't what Jack asked for. If you read the article he suggested (also happens to be the same article I reference first in my signature block as well) and follow the instructions in that article for posting table definitions and sample data you will find that you get much better help in solving your problem.