I always encounters a kind of problem in querying SQL which I am looking for a best practiced to solve it. In SELECT query like
SELECT field1, field2, ComplexFieldToBeCalculate, ....
there are fields that I want to get from complex calculation. I currently use two methods:
1. Get recordset with the ComplexFieldToBeCalculate not calculated, then recalculate the field by looping through all rows of the recordset. This method works all the time, but obviously not efficient.
2. Write function and use it like SELECT field1, field2, GetComplexField(field1, field2..), ....this method is good but function have some limitations like cannot execute dynamic SQL.
What is the best practice to get that kind of fields?