# Thread: in T-SQL can I use a formula from a table cell?

1. Registered User
Join Date
Jun 2011
Posts
19

## Unanswered: in T-SQL can I use a formula from a table cell?

I'm not sure if this can be done and also maybe what to call it to search the net for answers.

Basically I'm looking at a system where formulas have been added into fields in a table and I need to look at the field to see what formula to use when selecting eg: eg this + this, this / this etc.

Here's a basic table I have knocked up to try different things...

[FormulaCode] [varchar](10) NOT NULL
)
;

;
;
;
;
;

[OrderValue] [decimal](38, 20) NOT NULL
)
;

;
;
;

-- check table OK

-- These would go together as a SUM because of '321+456'
;
SELECT SUM(b.OrderValue) AS [OrderValue]
;

-- These would go together as a DIVIDE because of '321/456'
;
SELECT SUM(b.OrderValue) / SUM(c.OrderValue)
;

This is what the end result should be when run on the whole table.

-- End Result
CREATE TABLE #EndResultTable(
[OrderValue] [decimal](38, 20) NOT NULL
)
;

INSERT INTO #EndResultTable VALUES ('ORD123','100','20000')
;
INSERT INTO #EndResultTable VALUES ('ORD123','200','250')
;
INSERT INTO #EndResultTable VALUES ('ORD123','300','3')
;
INSERT INTO #EndResultTable VALUES ('ORD987','400','10000')
;
INSERT INTO #EndResultTable VALUES ('ORD987','500','2500')
;

SELECT * FROM #EndResultTable;

-- clean up
DROP TABLE #EndResultTable;

Any ideas or point me in the right direction or let me know if it isnt possible.

Thanks,

RC

2. Registered User
Join Date
Nov 2004
Posts
1,428
The only way I could think of is to create a dynamic SQL in a stored procedure.

3. Registered User
Join Date
Jun 2011
Posts
19
OK thanks Wim.

I'm not too hot on Dynamics SQL, only know the basics. Any tips on what I should roughly search the net for to get an idea how to work it out?

Thanks,
RC

4. Annie's Dog Walker
Join Date
Nov 2004
Location
on the wrong server
Posts
8,842
There are some here with higher mathmatical skills than myself, but is this not matrix mathmatics? I have done this in c# with multidimensional arrays. If I were you, I would google "matrix math in sql" and see what pops out.

5. Registered User
Join Date
Jun 2011
Posts
19
Thanks Thrasymachus,

Just googled 'matrix math in sql' and bloody hell
Looks like I'm in for a late evening reading over the web pages and working out math matrix.

RC

6. Registered User
Join Date
Nov 2004
Posts
1,428
Can you post the solution? I'm really curious.

7. Registered User
Join Date
Jun 2011
Posts
19
OK No problem when it's worked out will post it up. I'm meeting a friend next week that I think if I don't manage to find an answer by then, he might.

8. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
If you are running SQL 2012, you could use something like:
Code:
`SELECT TRY_Convert(int, '112/355')`
-PatP

9. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
`SELECT TRY_Parse(int, '112/355')`