User-defined currency type

  • cmartel 20772

    SSC Eights!

    Points: 937

    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?

  • Solomon Rutzky

    SSCoach

    Points: 16129

    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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply