Hi, I have a table like this, i want to calculate a formula was stored in column TURNOVER_FORMULAR, The formula depends on STK_CODE. How can i do it in SQL Server 2005 ?

# How can i calculate a formula was stored in sql?

**clickhere**#1

**gbritton**#2

google dynamic SQL. Basically you use the formula column to build a nvarchar(4000) string containing the SQL command with the formula, then execute the statement with sp_executesql

**clickhere**#3

Thanks gbritton,

I've used Dynamic Query, but SQL Server does not support sp_executesql in Function. I try using it with CURSOR but the process run very slow, because my data is over 10,000 records. I hope another way better.

**JamesK**#4

There is no way that I know of in SQL Server to compute the stored formulas like you have using a set based query. You are stuck with looping through the rows one at a time.

**clickhere**#5

Thanks for the reply JamesK, If no way at the moment, temporary i'll use CURSOR while waiting for another way with higher performance

**khtan**#6

if you are using stored procedure to insert / update to that table, it is best that you perform the calculation there rather than during retrieving of data

**clickhere**#7

Thanks for the reply, khtan

I have that table like picture as you've seen, i want upadate TURNOVER column. At first, i was using dynamic SQL but SQL does not support calling stored procedure from function. After that, i use Cursor and loop each rows, i looking for better idea because of looping over 10,000 records makes the process is running very slow

**khtan**#8

you are calculating the TURNOVER value during retrieval ? As mention in my previous post, why not calculate it during insert or update to that table ? you will only need to calculate it once

**clickhere**#9

Thanks khtan, that's good ideas, i'll try your way. sometimes, the formula is not exists when data was inserted to table, but i think i can control that situation.

**khtan**#10

that will be good. If not, you can consider using INSERT / UPDATE trigger to do that. But no matter what, you will still be calculating the value row by row.