It's good to put in a structure, and then whenever you need to "compare" you would call the function pointer. In the "sort" function call in one of the C's library you can see it, so you can compare apples to oranges to your heart's delight.
But what happens in SQL?
Let's say I have different funds (which I do) and they require different calculation according to their Fund type.
So funds A, B, C & D have calculation which is completely different the funds E, F & G.
Had I used a programming language like C I could say that a Fund could have a pointer to a function in their structure, and calculate of that. But how do I handle this in SQL?
I've designed a solution which would look like this:
I would add a field (column) to the Fund table called "CalcType" which would refer to the calculation type. Now, I can use the following code:
SELECT *
FROM PerformanceCalc1 (@param) --that's a function
INNER JOIN dbo.FUND
WHERE Fund.CalcType = 1
UNION ALL
SELECT *
FROM PerformanceCalc2 (@param) --that's a function
INNER JOIN dbo.FUND
WHERE Fund.CalcType = 2
UNION ALL
SELECT *
FROM PerformanceCalc3 (@param) --that's a function
INNER JOIN dbo.FUND
WHERE Fund.CalcType = 3
This calculation has its limitation: each Performance Calc functions run on all the funds, but the return set is limited to the appropriate Fund Type. The number of funds I have is as such that it doesn't really matter; if I had more it could have been an issue. Yet, what I like about this solution is that it is set based in its thinking, and not using Dynamics SQL (which requires elevated permissions of the end-user, if we want her to run it).
