SQLTeam.com | Weblogs | Forums

How can i calculate a formula was stored in sql?


#1

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 ?


#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


#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.


#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.


#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


#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


#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


#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


#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.


#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.