SQL Server, division returns zero

Here is 开发者_开发百科the code I'm using in the example:

 PRINT @set1
 PRINT @set2

 SET @weight= @set1 / @set2;
 PRINT @weight

Here is the result:

47
638
0

I would like to know why it's returning 0 instead of 0,073667712

Either declare set1 and set2 as floats instead of integers or cast them to floats as part of the calculation:

SET @weight= CAST(@set1 AS float) / CAST(@set2 AS float);

When you use only integers in a division, you will get integer division. When you use (at least one) double or float, you will get floating point division (and the answer you want to get).

So you can

1. declare one or both of the variables as float/double
2. cast one or both of the variables to float/double.

Do not just cast the result of the integer division to double: the division was already performed as integer division, so the numbers behind the decimal are already lost.

Simply mutiply the bottom of the division by 1.0 (or as many decimal places as you want)

PRINT @set1 
PRINT @set2 
SET @weight= @set1 / @set2 *1.00000; 
PRINT @weight

Because it's an integer. You need to declare them as floating point numbers or decimals, or cast to such in the calculation.

if you declare it as float or any decimal format it will display

0

only

E.g :

declare @weight float;

SET @weight= 47 / 638; PRINT @weight

Output : 0

If you want the output as

0.073667712

E.g

declare @weight float;

SET @weight= 47.000000000 / 638.000000000; PRINT @weight

In SQL Server direct division of two integer returns integer even if the result should be the float. There is an example below to get it across:

--1--
declare @weird_number_float float
set @weird_number_float=22/7
select @weird_number_float

--2--
declare @weird_number_decimal decimal(18,10)
set @weird_number_decimal=22/7 
select @weird_number_decimal

--3--
declare @weird_number_numeric numeric
set @weird_number_numeric=22/7 
select @weird_number_numeric

--Right way

declare @weird_number float
set @weird_number=cast(22 as float)/cast(7 as float)
select @weird_number

Just last block will return the 3,14285714285714. In spite of the second block defined with right precision the result will be 3.00000.