divide by 0

1. Senior Member
Join Date
Dec 1969
Posts
874

## divide by 0

Good Monday to you all.....I&#039;ve got this statement in my select clause; it returns four rows then throws an exception whenever MTDgross_budget is =0....how can I deal with this in my stored procedure? <BR>Many thanks.<BR>Select blah, blah,<BR>MTDGross/MTDgross_budget*100 as MTDGrossBudgetPercent<BR><BR>Error:Divide by zero error encountered

2. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: divide by 0

Where MTDgross_budget &#062; 0 <BR><BR>Where MTDgross_budget is not null<BR>

3. Senior Member
Join Date
Dec 1969
Posts
874

## RE: divide by 0

if the variable is = to 0 I want to return 0 something like this:<BR><BR>if MTDgross_budget=0 then<BR>MTDGrossBudgetPercent=0<BR>else<BR>MTDGros s/MTDgross_budget*199 as MTDGrossBudgetPercent<BR>end if<BR><BR>how can I accomplish this in a SP?

4. God
Senior Member
Join Date
Dec 1969
Posts
18,177

## RE: divide by 0

case when isnull(mtdgross_budget, 0) = 0 then 0<BR> else MTDGross/MTDgross_budget*199 as MTDGrossBudgetPercent<BR>end

5. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: divide by 0

I agree with God&#039;s code<BR>BUT I am confused as to why your not just eliminating those rows in the where clause?

6. Senior Member
Join Date
Dec 1969
Posts
874

## RE: divide by 0

yep I have no idea.........just turning somone else&#039;s ASP into asp.net.....what hell this is!!!!!!!

7. Senior Member
Join Date
Dec 1969
Posts
874

## RE: divide by 0

guys I&#039;m trying to use the SELECT CASE in my query but keep getting syntax an error<BR><BR>select....<BR>YTDnet/netytd * 100 as YTDnetpercent,<BR>MTDNet/net * 100 as MTDnetpercent,<BR>select<BR> CASE MTDgross_budget<BR> WHEN = 0 THEN MTDGrossBudgetPercent = 0<BR> ELSE MTDGross/MTDgross_budget * 100 as MTDGrossBudgetPercent <BR> END<BR>MTDnet/MTDnet_budget*100 as MTDNetBudgetPercent, <BR>********************************************** ********<BR>entire stored procedure:<BR><BR>CREATE PROCEDURE RESP_productions AS<BR><BR>Declare @yy varchar(100), @mm varchar(100)<BR>select @yy =(SELECT right(Nb15year,2) FROM parms, months where parms.nb15month=months.monthno)<BR>select @mm =(SELECT nb15month FROM parms, months where parms.nb15month=months.monthno)<BR><BR><BR>select units.agencygroup,MTDGross,MTDNet,MTDgross_budget, MTDnet_budget, YTDgross_budget,<BR>YTDnet_budget, YTDgross, YTDnet,groupname, <BR><BR>MTDGross/gross *100 as MTDgrosspercent, <BR>YTDgross/grossytd * 100 as YTDgrosspercent,<BR>YTDnet/netytd * 100 as YTDnetpercent,<BR>MTDNet/net * 100 as MTDnetpercent,<BR><BR><BR>select<BR> CASE MTDgross_budget<BR> WHEN = 0 THEN MTDGrossBudgetPercent = 0<BR> ELSE MTDGross/MTDgross_budget * 100 as MTDGrossBudgetPercent <BR> END<BR><BR>--MTDGross/MTDgross_budget*100 as MTDGrossBudgetPercent,<BR>MTDnet/MTDnet_budget*100 as MTDNetBudgetPercent, <BR>YTDnet/YTDnet_budget*100 as YTDnetBudgetPercent, <BR>YTDGross/YTDgross_budget*100 as YTDGrossBudgetPercent,<BR>gross<BR>from <BR>--get MTDgross percent-query used to get &#039;MTD gross&#039; an &#039;MTDnet percent&#039;<BR>(select sum(gross) as gross, sum(net) as net from grossnet where yymm=@yy+@mm) as<BR>MTDgrossPercent,<BR>--end get MTDgross percent <BR>--get MTDgross percent-query used to get &#039;YTD gross&#039; an &#039;YTDnet percent&#039;<BR>(select sum(gross) as grossytd, sum(net) as netytd from grossnet where yymm&#062;@yy+00 and <BR>yymm&#060;=@yy+@mm) as YTDpercent,<BR>--end get YTDgross percent <BR>(select agencygroup.agencygroup as agencygroup,sum(coalesce(mtdtempgross,0)) as<BR>MTDgross,sum(coalesce(mtdtempgross,0))*2 as MTDgross2,sum(coalesce(mtdtempnet,0)) as MTDnet <BR>from agencygroup left join (select agencygroup, sum(gross) as mtdtempgross, sum(net) as<BR>mtdtempnet from grossnet inner join branchinfo on agency=agencyno where yymm=@yy+@mm group by<BR>agencygroup) table1 on agencygroup.agencygroup=table1.agencygroup group by<BR>agencygroup.agencygroup) Units<BR>inner join<BR>(select aggroup as agencygroup,sum(gross) as MTDgross_budget, sum(net) as MTDnet_budget from<BR>respbudget2002 where yymm=@yy+@mm group by aggroup ) Budget<BR>on Units.agencygroup = Budget.agencygroup<BR>inner join<BR>(select aggroup, coalesce(sum(gross),0) as YTDgross_budget, coalesce(sum(net),0) as<BR>YTDnet_budget from respbudget2002 where yymm&#062;@yy+00 and yymm&#060;=@yy+@mm group by aggroup)YTD<BR>on Units.agencygroup = YTD.aggroup<BR>inner join<BR>(select agencygroup.agencygroup as agencygroup, coalesce(sum(ytdtempgross),0) as YTDgross,<BR>coalesce(sum(ytdtempnet),0) as YTDnet from agencygroup left join ( select agencygroup,<BR>sum(gross) as ytdtempgross, sum(net) as ytdtempnet from grossnet inner join branchinfo on<BR>agency=agencyno where yymm&#062;@yy+00 and yymm&#060;=@yy+@mm group by agencygroup) table1 on<BR>agencygroup.agencygroup=table1.agencygroup group by agencygroup.agencygroup)YTDgross<BR>on YTD.aggroup=YTDgross.agencygroup<BR>inner join<BR>(select avg(agencygroup) as agencygroup, groupname as groupname from agencygroup group by<BR>groupname)groupnames<BR>on YTDgross.agencygroup = groupnames.agencygroup<BR><BR>--where<BR><BR>--budget.MTDgross_budget &#062;0<BR><BR><BR>--and <BR>-- budget.MTDnet_budget &#062; 0<BR>--and <BR>--YTD.YTDgross_budget &#062; 0<BR>--and <BR>--YTD.YTDnet_budget &#062; 0<BR><BR><BR><BR>order by groupnames.agencygroup<BR>GO

8. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: divide by 0

**** that is one BIG *** SP!<BR><BR>1) Have you tried it in Query Analyser?<BR><BR>2) Have you cut the Select ... and run that with Values in the Query Analyser?<BR><BR>Actually the second will tell you more info as to where the error is!<BR>

9. Senior Member
Join Date
Dec 1969
Posts
874

## RE: divide by 0

big *** in deed...I&#039;m taking each individual on an asp page into on SP...for the dg.<BR><BR>anyway I got this working<BR>MTDGrossBudgetPercent = CASE WHEN MTDgross_budget &#060; 0 THEN 0 ELSE MTDGross/MTDgross_budget * 100 END,<BR>

10. God
Senior Member
Join Date
Dec 1969
Posts
18,177

## RE: divide by 0

[code language="T-SQL"]select.... <BR>YTDnet/netytd * 100 as YTDnetpercent, <BR>MTDNet/net * 100 as MTDnetpercent, <BR> CASE MTDgross_budget <BR> WHEN = 0 THEN MTDGrossBudgetPercent = 0 <BR> ELSE MTDGross/MTDgross_budget * 100 as MTDGrossBudgetPercent <BR>END <BR>MTDnet/MTDnet_budget*100 as MTDNetBudgetPercent,<BR>more_Fields,<BR>FROM table..[/code]

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•