Dynamically declare variable (numeric)

Question

Dynamically declare variable (numeric)

jjerome00

Say Hey Kid

Points: 706
More actions
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!

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

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

Anders Pedersen SSChampion Points: 11410 More actions · Answer 1

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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

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.

jjerome00 Say Hey Kid Points: 706 More actions · Answer 3

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)

jjerome00 Say Hey Kid Points: 706 More actions · Answer 4

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

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!!

jjerome00 Say Hey Kid Points: 706 More actions · Answer 6

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.

steve smith-401573 SSChampion Points: 10889 More actions · Answer 7

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?

jjerome00 Say Hey Kid Points: 706 More actions · Answer 8

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!