divide by zero error encountered

Question

i am using vs2015 and ms sql.. my database table definition on: [REG_NO] VARCHAR (50) NOT NULL [NAME] VARCHAR (25) NULL [ROLL_NO] VARCHAR (10) NULL [DOB] VARCHAR (50) NULL [COURSE] VARCHAR (10) NULL [DEPARTMENT] VARCHAR (20) NULL [YEAR] NVARCHAR (10) NULL [SHIFT] VARCHAR (10) NULL [IMAGE] IMAGE NULL [PRESENTEDHOURS] FLOAT (53) NULL [TOTALHOURS] FLOAT (53) NULL PRIMARY KEY CLUSTERED ([REG_NO] ASC) i will add a column on : [PERCENTAGE] AS (([PRESENTED HOURS]/[TOTAL HOURS])*(100)) now my table definition is [REG_NO] VARCHAR (50) NOT NULL [NAME] VARCHAR (25) NULL [ROLL_NO] VARCHAR (10) NULL [DOB] VARCHAR (50) NULL [COURSE] VARCHAR (10) NULL [DEPARTMENT] VARCHAR (20) NULL [YEAR] NVARCHAR (10) NULL [SHIFT] VARCHAR (10) NULL [IMAGE] IMAGE NULL [PRESENTEDHOURS] FLOAT (53) NULL [TOTALHOURS] FLOAT (53) NULL [PERCENTAGE] AS (([PRESENTED HOURS]/[TOTAL HOURS])*(100)) PRIMARY KEY CLUSTERED ([REG_NO] ASC) then run................ error message on ....... divide error encountered by zero

Nitin Sontakke · Answer

I just tried this and it does seem to work: create table sometable ( [REG_NO] VARCHAR (50) NOT NULL  [ NAME ] VARCHAR (25) NULL  [ROLL_NO] VARCHAR (10) NULL  [DOB] VARCHAR (50) NULL  [COURSE] VARCHAR (10) NULL  [DEPARTMENT] VARCHAR (20) NULL  [ YEAR ] NVARCHAR (10) NULL  [SHIFT] VARCHAR (10) NULL  [IMAGE] IMAGE NULL  [PRESENTED HOURS] FLOAT (53) NULL  [TOTAL HOURS] FLOAT (53) NULL  [PERCENTAGE] AS (([PRESENTED HOURS]/ ( case when isnull ([TOTAL HOURS] 0) = 0 then 1 else [TOTAL HOURS] end ))*(100)) PRIMARY KEY CLUSTERED ([REG_NO]) ) insert into sometable ([REG_NO] [PRESENTED HOURS] [TOTAL HOURS]) values (1 5 0) insert into sometable ([REG_NO] [PRESENTED HOURS] [TOTAL HOURS]) values (2 5 100) select * from sometable

Nilesh Shah · Answer

a row having [TOTALHOURS] value of zero will throw this error instead of this make this field as FLOAT then you can try updating this field in an after insert/after update trigger