My database already contains a few CLR functions (string aggregation and string manipulation functions) that run fast and well.
I would like to create a «MyCurrency» user defined type that would manage both the amount and the currency part. I do not intend to physically store MyCurrencies in my database. I would however create MyCurrency columns in variable tables and I need to be able to run aggregate and math functions on them. C# code would detect multiple currencies and act accordingly. For example SUM of 1USD and 2USD would be 3USD but SUM of 1USD and 1CAD might return NULL or 2???.
Did anyone ever created such a user defined type? Can we aggregate on it?