Post reply

Dynamically declare variable (numeric)

  • August 3, 2007 at 10:24 am

    #179932

    I'm trying to dynamically declare a variable using the definition of a column in another table. More specifically, I'm trying to declare a numeric variable with a custom precision and scale. Here are some methods I've tried:

    I tried loading the precision and scale of the source column into variables (using information_schema.columns), then declaring the variable as below, no luck:

    declare @numVar as numeric(@prec,@scale);

    I tried creating a temp table using the source column to create 3 columns with the correct precision I'm looking for. But to get this to work I have to create a string that I use Execute() on, and I can't access the temp table afterwords.

    declare @sql varchar(1000);

    declare @column varchar(50);

    declare @table varchar(50);

    set @column = 'weight'; --(numeric(18,4) or similar)

    set @table = 'products';

    set @sql = 'select top 1 ' + @column + ',' + @column + ' as [conv], ' + @column + ' as [pounds] into #PoundConv from ' + @table;

    print @sql

    execute(@sql);

    select * from #PoundConv

    Any suggestions you could provide would be GREATLY appreciated. Thanks in advance!

  • August 3, 2007 at 10:43 am

    #724585

    Maybe you should look into sql_variant data type?

    If not I think you would have to encapsulate your entire query in the same string to execute as where you declare your data type.

    (I am in no way endorsing the use of the SQL_variant data type, just noting that it might be a possiblity here)

  • August 3, 2007 at 10:47 am

    #724588

    You can't access the # temp table after your exec (@sqlcmd) because the # temp table no longer exists, it is dropped after the exec completes.  You could change your # temp table to a ## temp table, but you will want to drop it explicitly when you are done.

     

  • August 3, 2007 at 10:55 am

    #724590

    Thanks for your reply.

    I'm not sure sql_variant would really help in my case. I'm more concerned with calculating values using the same precision and scale than I am with accepting those values into a variable.

    I want to multiply two numbers together and want to ensure that the second number (multiplier?) has the exact same precision and scale as the source number.

    Please excuse my strange pseudocode:

    [firstnumber] (numeric(18,4) or whatever the column is)

    *

    [SecondNumber] (converted to same precision and scale as firstnumber)

  • August 3, 2007 at 11:03 am

    #724593

    Thanks Lynn! I think this might do it.

    I have to admit I didn't know about the ## temp table. I got into the habit of dropping my temp tables when I was finished with them a while ago, so I don't see too much of a problem using this syntax.

  • August 3, 2007 at 11:08 am

    #724594

    Here's one question.  Why are you doing this server side?  I know you can find some trick or tweak, but this seems to be a problem more suited for excel or the likes!!

  • August 3, 2007 at 12:15 pm

    #724604

    I'm trying to implement a way to convert between pounds and kilograms in an existing application (C++) that heavily relies on the database.

    Values are stored in the database using different precision and scale. Our conversion value for KG (2.204623) needs to match the same scale as the value we are changing. Otherwise, when we store the value back in the number will be "chopped off" and there will be additional error exposed when we grab the value back out of the database and reconvert it back in KG.

    If you try converting 1 kg into # and storing it into a 4 decimal field, you can't use a 6 decimal conversion number (2.204623). When you read the value back out of the database the value will read incorrectly as .9.

    Here's my math showing the error:

    [Writing to DB]

    1 kg * 2.204623 = 2.204623 (stored into 18.4) = 2.2046 #

    [Reading from DB]

    2.2046 / 2.204623 = .9999 kg (this won't be rounded up)

    Here it is if I keep the scale/decimal the same:

    [Writing to DB]

    1 kg * 2.2046 = 2.2046 (stored into 18.4) = 2.2046 #

    [Reading from DB]

    2.2046 / 2.2046 = 1 kg

    Hope this helps explain things. I hope to have a function built by the end of the day.

  • August 3, 2007 at 12:37 pm

    #724609

    isn't there already a built-in round function? using what you already know for the precision, can't you specify that value in the round function?

  • August 3, 2007 at 4:23 pm

    #724660

    Actually, you are right. The round function would be my friend in this case, and it allows me to use a variable to set the Scale. Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

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