September 12, 2016 at 3:14 pm
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?
September 12, 2016 at 3:51 pm
cmartel 20772 (9/12/2016)
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?
Hi there. Technically this can be done. They can be aggregated through a SQLCLR User-Defined Aggregate (UDA) as it can access the underlying definition of the Type and then you can code it for whatever rules you like. However, I don't see any benefit to doing all of the work to do this (creating the UDT and the UDA) when two columns -- one being MONEY and the other being a TINYINT or SMALLINT for CurrencyTypeID -- would get you the same thing. And then it would be very easy to do aggregations and split out currency types, or enforce that they all have to be the same, etc. AND it would be a lot more efficient than doing the same thing via a User-Defined Type (UDT).
Hope this helps. Take care, Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply