SQLTeam.com | Weblogs | Forums

How can i calculate a formula was stored in sql?


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 ?


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


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.


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.


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


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


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


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


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.


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.