Variant to Decimal type

12 Nov 2005 10:10 PM

Scott

My code returns 48 because I'm setting @mypercent as an integer. I need it
to return 1 decimal point and return 48.2.
I tried different decimal types but got errors. What should my final line of
code be to return 48.2?

CODE:

declare @myvalue1 as int
declare @myvalue2 as int
declare @mypercent sql_variant

set @myvalue1 = '1181402'
set @myvalue2 = '56974267'
set @mypercent = (@myvalue2/@myvalue1)
select @mypercent as int

note: I'm simulating some longer code in case anyone is confused why I'm
using the variant type for this simple problem

12 Nov 2005 11:43 PM

ML

The problem is you require a decimal result, yet none of your operands are
decimal.

declare @myvalue1 as decimal(12,1)
declare @myvalue2 as decimal(12,1)
declare @mypercent sql_variant -- ???

set @myvalue1 = 1181402
set @myvalue2 = 56974267
set @mypercent = cast((@myvalue2/@myvalue1) as decimal(12, 1))
select @mypercent as int

Or:

declare @myvalue1 as int
declare @myvalue2 as int
declare @mypercent sql_variant -- ???

set @myvalue1 = 1181402
set @myvalue2 = 56974267
set @mypercent = cast((cast(@myvalue2 as decimal(12, 1))/@myvalue1) as
decimal(12, 1))
select @mypercent as int

ML

13 Nov 2005 4:55 PM

Scott

This works, but I would like to understand why in your 1st example would we
declare @myvalue1 and @myvalue2 as decimal(12,1) when they are clearly int
type? As stated, this fixes my problem, I'm just trying to learn.

Show quote

"ML" <M*@discussions.microsoft.com> wrote in message
news:E5C14DF2-38D1-41F2-B332-B644A39C1AB3@microsoft.com...
> The problem is you require a decimal result, yet none of your operands are
> decimal.
>
> declare @myvalue1 as decimal(12,1)
> declare @myvalue2 as decimal(12,1)
> declare @mypercent sql_variant -- ???
>
> set @myvalue1 = 1181402
> set @myvalue2 = 56974267
> set @mypercent = cast((@myvalue2/@myvalue1) as decimal(12, 1))
> select @mypercent as int
>
> Or:
>
> declare @myvalue1 as int
> declare @myvalue2 as int
> declare @mypercent sql_variant -- ???
>
> set @myvalue1 = 1181402
> set @myvalue2 = 56974267
> set @mypercent = cast((cast(@myvalue2 as decimal(12, 1))/@myvalue1) as
> decimal(12, 1))
> select @mypercent as int
>
>
> ML

13 Nov 2005 6:59 PM

ML

Integer data types => integer math => integer results.

You cannot achieve decimal results through integer math, that's why at least
one of the operands needs to be decimal for the result to be decimal.

And another thing: if the numbers you've posted really are - as you say -
integers why did you enclose them in single quotes then?

The fact that you declare the result variable as sql_variant is another
curiosity, yet you mention that we should ignore it.

The principles of mathematics set the base for all computer logic, and you
really, really, really should take them into account.

ML