my problem in detail

How I can rectify divide by zero exception in stored procedure

By checking first, to make sure you're not about to divide by zero ? I suspect a CASE statement is the easiest way to do this.

|||i know it, but actually I want that display 0 or my error message instead of server error message.|||

OK - so what do you need to know ? In your stored proc, return the result of the divide if it's not going to divide by zero, or zero if it is. Then you can check for 0 and show an error if you prefer.

|||

when i write Query like

select 4/0 then it display error msg - 8134

but i don't wan't to display it, i want it as 0.

i have too much table like apr0405i, aprs0506i similary as years wise table. So i create sp and passing '0405' value of parameter of sp which select the table of corresponding year then it display value. but in 0506 then it display the divide by zero exception. so i want to suppress this error message and display 0. because it can't possible to use case with big sql query since i am using more than 15 sql query in one query statement and stored that value in parameter & then print value of paramter

|||

OK - so your database design is not so great ? You create a table for each month ?

I don't know of any way to make SQL Server return 0 when you divide by 0.

|||Joining this thread a little late, oh well....

As far as I am aware, you can't make SQL Server return 0 but you can override the default behaviour (suppressing error messages and the like) and define your own behaviour, such as returning 0. For example:

/*
Override default behaviour
*/
SET ARITHABORT OFF -- Divide by 0 does not halt execution
SET ARITHIGNORE ON -- Divide by 0 does not display error message
SET ANSI_WARNINGS OFF -- Divide by 0 does not display error message

DECLARE @.returnOfCalc As INT

SELECT @.returnOfCalc = 4/0
IF @.returnOfCalc IS NULL
BEGIN
SET @.returnOfCalc = 0
PRINT @.returnOfCalc -- Optional, just as a demo of what can be done
END

/*
Restore default behaviour
*/
SET ARITHABORT ON
SET ARITHIGNORE OFF
SET ANSI_WARNINGS ON

So in your stored procedure, you would issue the first bunch of SET statements at the beginning of the procedure (to override default behaviour) and just before the end of the stored procedure you would issue the second set of SET statements (to restore the default behaviour).

I really don't suggest you mess with the default behaviour in SQL Server, but if you really want/need to then knock yourself out. Just remember to be a good citizen and reset the default behaviour when you finish.

Hope that helps a bit, but sorry if it doesn't
|||

You can do something like:

select coalesce(@.i/nullif(@.j, 0), 0)

-- or

select case @.j when 0 then @.j else @.i/@.j end

|||

use the try... catch

feature of sql2k5

|||

Thanks for ur suggestion, but I already solved it by

case method it goes too much bigger but it gives satisfied results

well, ur answer giving very well theoritical concept, Thank u once again, Next I will send u another query, if i have

|||Thank U chandra ji once again, i was already using same ideas which u send. But Its very well felling inside me, Thanks once again