* Microsoft SQL Server 2008 R2
Warning Message :
"Msg 8134, Level 16, State 1, Line 1. Divide by zero error encountered."
1. Example Table "Temp_DivideAmount"
----------------------------------------------------------
----------------------------------------------------------
-----CODE START
CREATE TABLE [dbo].[Temp_DivideAmount]
(
[ID] [varchar](5) NOT NULL,
[Amount_1] int,
[Amount_2] int
) ON [PRIMARY]
-----CODE END
----------------------------------------------------------
----------------------------------------------------------
2. Create a sampling data,
----------------------------------------------------------
----------------------------------------------------------
-----CODE START
insert into Temp_DivideAmount values ('1','100','0')
insert into Temp_DivideAmount values ('2','50','0')
insert into Temp_DivideAmount values ('3','0','100')
insert into Temp_DivideAmount values ('3','20','100')
insert into Temp_DivideAmount values ('3','50','2')
-----CODE END
----------------------------------------------------------
----------------------------------------------------------
3. To see the data,
----------------------------------------------------------
----------------------------------------------------------
-----CODE START
select * from Temp_DivideAmount
-----CODE END
----------------------------------------------------------
----------------------------------------------------------
4. When run this query, display warning message
"Msg 8134, Level 16, State 1, Line 1. Divide by zero error encountered."
----------------------------------------------------------
----------------------------------------------------------
-----CODE START
select
ID
,Amount_1
,Amount_2
,Amount_1 / Amount_2 * 100 as Total_Divide
from Temp_DivideAmount
-----CODE END
----------------------------------------------------------
----------------------------------------------------------
5. To fix the warning message, type the new query below,
----------------------------------------------------------
----------------------------------------------------------
-----CODE START
select
ID
,Amount_1
,Amount_2
,case
when isnull(Amount_1,0) =0 and isnull(Amount_2,0) =0 then 0
when isnull(Amount_1,0) =0 and isnull(Amount_2,0) <>0 then 0
when isnull(Amount_1,0) <>0 and isnull(Amount_2,0) =0 then 1
when isnull(Amount_1,0) <>0 and isnull(Amount_2,0) <>0
then (isnull(Amount_1,0)/isnull(Amount_2,0))
end *100 AS Total_Divide
from Temp_DivideAmount
-----CODE END
----------------------------------------------------------
----------------------------------------------------------
6. Optional, to remove the table
------------------------------------
------------------------------------
-----CODE START
drop table Temp_DivideAmount
-----CODE END
-------------------------------------
-------------------------------------