Hiding error messages
-
Hi.
First post here, I hope it's in the right forum... We've got an app. calling a SP. The problem is that the SP can generate division by 0 ( "Select 150/0" for example)
We can catch the error but sqlServer always show a popup window saying "Error: division by 0". Is there a way to dactivate the popups when an sqlserver error occurs?
Thank you. And please forgive me for my english, I'm french (sorry
) -
I'm not sure if I completely understood your problem -- if you can handle the error in the stored procedure then there should be no problem (i.e. within the stored proc check if a divide by zero error occurs and in such a case set the denominator to 1)
If you want to suppress division by zero error messages then you can use a combination of SET ANSI_WARNINGS OFF and SET ARITHABORT OFF within the stored procedure -- this returns a null value when a divide by zero occurs...
sample script written below:
USE pubs
GO
IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'Tab1')
DROP TABLE dbo.Tab1
IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'SP1')
DROP PROCEDURE dbo.SP1
CREATE TABLE dbo.Tab1(Val1 INT,Val2 INT)
INSERT dbo.Tab1(Val1)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UPDATE dbo.Tab1 SET Val2 = Val1%2
GO
CREATE PROCEDURE dbo.SP1
AS
BEGIN
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
SELECT Val1,Val2 FROM dbo.Tab1
-- set denominator to 1 when 0
SELECT Val1/(CASE Val2 WHEN 0 THEN 1 ELSE Val2 END) FROM dbo.Tab1
-- no division by zero error due to ARITHABORT and ANSI_WARNINGS set to OFF
SELECT Val1/Val2 FROM dbo.Tab1
END
GO
EXEC dbo.SP1
DROP TABLE dbo.Tab1
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply