locked
How can I store mutiple precision in a single table? RRS feed

  • Question

  • Hello,

    If I insert some numbers of varying scale,such as 2.30, 2.3, into a decimal(20,8) column, when I read it back from DB, can I still get different scale?

    I'v tried in SQL Server  In SQL server what I got is 2.30000000 and 2.30000000,

    Can anyone tell me whether How can I store my values with its original precesion?

    Nilkanth S. Desai

    Thursday, July 26, 2012 12:23 AM

Answers

  • Is this for display purposes?  Or for math reasons?  You could use a sql_variant, and that would allow for different typing of data in the same column. It is tedious (but any solution where you want to do what you want to do will be,) but it will work:

    DECLARE @numericVariant sql_variant = 20.8;

    SELECT @numericVariant AS numericVariant,
       SQL_VARIANT_PROPERTY(@numericVariant,'BaseType') as baseType,
       SQL_VARIANT_PROPERTY(@numericVariant,'Precision') as precision,
       SQL_VARIANT_PROPERTY(@numericVariant,'Scale') as scale;
    GO

    Returns:

    numericVariant baseType precision scale
    20.8         numeric     3         1

    DECLARE @numericVariant sql_variant = 20.8000;

    SELECT @numericVariant AS numericVariant,
       SQL_VARIANT_PROPERTY(@numericVariant,'BaseType') as baseType,
       SQL_VARIANT_PROPERTY(@numericVariant,'Precision') as precision,
       SQL_VARIANT_PROPERTY(@numericVariant,'Scale') as scale;

    Returns:
    numericVariant baseType precision scale
    20.8000         numeric     6         4

    If just for display, you could declare a mask to format the values with and format in the app, but this is the more math acceptable. However, the math won't work using common T-SQL:


    DECLARE @numericVariant sql_variant = 20.8000;
    DECLARE @numericVariant2 sql_variant = 20.8000;

    SELECT @numericVariant * @numericVariant

    You will get an error:

    Msg 8117, Level 16, State 1, Line 4
    Operand data type sql_variant is invalid for multiply operator.

    So you are kinda stuck I think.  Maybe with a bit more why you want to do this we can come up with a solution that will help?


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Thursday, July 26, 2012 12:35 AM