Wie vermeiden Sie die "divide by zero" Fehler in SQL?

Ich habe diese Fehlermeldung:

Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. 

Was ist der beste path, um SQL-Code zu schreiben, damit ich diese Fehlermeldung nie wieder sehen werde?

Ich könnte folgendes tun:

  • Füge eine where-Klausel hinzu, damit mein Divisor niemals Null ist

Oder

  • Ich könnte eine Fall-statement hinzufügen, so dass es eine spezielle Behandlung für Null gibt.

Ist der beste path, eine NullIf-Klausel zu verwenden?

Gibt es einen besseren path, oder wie kann das durchgesetzt werden?

Um einen "Division by zero" Fehler zu vermeiden, haben wir es so programmiert:

 Select Case when divisor=0 then null Else dividend / divisor End ,,, Wählen Sie Fall, wenn divisor = 0 dann null ist Select Case when divisor=0 then null Else dividend / divisor End ,,, Else Dividenden / Divisor Select Case when divisor=0 then null Else dividend / divisor End ,,, 

Aber hier ist eine viel schönere Art, es zu tun:

 Select dividend / nullif(divisor, 0) ... 

Jetzt ist das einzige Problem, sich an das NullIf-Bit zu erinnern, wenn ich die "/" – button verwende.

Falls du null zurückkehren willst, falls eine Null-Devision passieren würde, kannst du:

 SELECT COALESCE(dividend / NULLIF(divisor,0), 0) FROM sometable 

Für jeden Divisor, der null ist, erhältst du eine Null in der Ergebnismenge.

Dies schien die beste Lösung für meine Situation zu sein, wenn man versucht, die Teilung durch Null zu adressieren, was in meinen data passiert.

Angenommen, Sie wollen die männlich-weiblichen Verhältnisse für verschiedene Schulklubs berechnen, aber Sie entdecken, dass die folgende Abfrage fehlschlägt und einen Divid-by-Null-Fehler ausgibt, wenn es versucht, das Verhältnis für den Herr des Rings Club zu berechnen, der keine Frauen hat :

 SELECT club_id, males, females, males/females AS ratio FROM school_clubs; SELECT club_id, Männer, Weibchen, Männer / Frauen AS-Verhältnis SELECT club_id, males, females, males/females AS ratio FROM school_clubs; 

Sie können die function NULLIF , um die Division durch Null zu vermeiden. NULLIF vergleicht zwei Ausdrücke und gibt null zurück, wenn sie gleich sind oder der erste Ausdruck anders ist.

Umfrage als:

 SELECT club_id, males, females, males/NULLIF(females, 0) AS ratio FROM school_clubs; SELECT club_id, Männer, Weibchen, Männer / NULLIF (Weibchen, 0) AS-Verhältnis SELECT club_id, males, females, males/NULLIF(females, 0) AS ratio FROM school_clubs; 

Jede Zahl, die durch NULL geteilt wird, gibt NULL , und es wird kein Fehler erzeugt.

Sie können dies auch am Anfang der Abfrage machen:

 SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET ARITHABORT AUS SET ARITHABORT OFF SET ANSI_WARNINGS OFF 

Also, wenn Sie so etwas wie 100/0 haben, wird es NULL zurückgeben. Ich habe dies nur für einfache Fragen getan, also weiß ich nicht, wie es sich auf länger / komplexe auswirken wird.

EDIT: Ich bekomme eine Menge von Downvotes auf diese vor kurzem … so dachte ich, ich würde nur eine Notiz hinzufügen, dass diese Antwort geschrieben wurde, bevor die Frage wurde es am neuesten bearbeiten, wo die Rückgabe Null wurde als Option hervorgehoben .. was scheint sehr akzeptabel. Ein Teil meiner Antwort wurde an Bedenken wie die von Edwardo gerichtet, in den Kommentaren, die schien, sich für die Rückkehr einer 0 zu befürworten. Dies ist der Fall, den ich angelte.

ANTWORT: Ich denke, es gibt eine zugrunde liegende Frage hier, was ist die Teilung von 0 ist nicht legal. Es ist ein Hinweis darauf, dass etwas grundsätzlich falsch ist. Wenn du dich null teufelst, versuchst du etwas zu tun, was nicht mathematisch sinnvoll ist, also keine numerische Antwort, die du bekommen kannst, ist gültig. (Die Verwendung von null in diesem Fall ist vernünftig, da es sich nicht um einen Wert handelt, der in späteren mathematischen Berechnungen verwendet wird).

Also fragt Edwardo in den Kommentaren "was ist, wenn der Benutzer eine 0 einlegt?", Und er befürwortet, dass es in Ordnung sein sollte, um eine 0 im Gegenzug zu bekommen. Wenn der Benutzer null in die Menge setzt und du willst 0 zurückgegeben, wenn sie das tun, dann solltest du Code auf die Geschäftsregeln Ebene setzen, um diesen Wert zu fangen und zurückzukehren 0 … nicht einen speziellen Fall, wo Division durch 0 = 0

Das ist ein subtiler Unterschied, aber es ist wichtig … weil das nächste Mal jemand nennt deine function und erwartet, dass es das Richtige zu tun, und es tut etwas funky, das ist nicht mathematisch korrekt, sondern nur behandelt die besondere Rand Fall ist es ein gute Chance, jemanden später zu beißen. Sie sind nicht wirklich durch 0 … Sie sind gerade eine schlechte Antwort auf eine schlechte Frage.

Stellen Sie sich vor, ich kodiere etwas, und ich schraube es auf. Ich sollte in einer Strahlungsmessung Skalierung Wert zu lesen, aber in einem seltsamen Rand Fall habe ich nicht vorwegnehmen, lese ich in 0. Ich lege dann meinen Wert in Ihre function … Sie geben mir eine 0! Hurra, keine Strahlung! Außer es ist wirklich da und es ist nur so, dass ich in einem schlechten Wert war … aber ich habe keine Ahnung. Ich möchte Division, um den Fehler zu casting, weil es die Fahne ist, dass etwas nicht stimmt

Sie können die Abfrage zumindest mit einem Fehler brechen und NULL wenn es eine Division durch Null gibt:

 SELECT a / NULLIF(b, 0) FROM t 

Allerdings würde ich NIEMALS dieses in Zero mit coalesce umwandeln, wie es in dieser anderen Antwort gezeigt wird, die viele upvotes bekam. Das ist im mathematischen Sinne völlig falsch, und es ist sogar gefährlich, da Ihre Bewerbung wahrscheinlich falsch und irreführende Ergebnisse zurückbringen wird.

 SELECT Dividend / ISNULL(NULLIF(Divisor,0),1) AS Result 

Ich schrieb eine function eine Weile zurück, um es für meine gespeicherten Prozeduren zu behandeln :

 print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go drucken 'SafeDivide Stored Proc ... erstellen' print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go falls vorhanden (select * from dbo.sysobjects wo name = 'safeDivide') drop function safeDivide; print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go create function dbo.safeDivide (@Numerator decimal (38,19), @divisor dezimal (39,19)) print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go liefert Dezimalzahl (38,19) print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go - ********************************************* *************************** print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go - Vorgehensweise: safeDivide () print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go - Autor: Ron Savage, Zentral, ex: 1282 print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go - date: 22.06.2004 print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go - print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go - Diese function teilt das erste Argument nach dem zweiten Argument nach print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go - Überprüfung auf NULL oder 0 Divisoren, um "divide by zero" Fehler zu vermeiden. print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go - print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go - date Init. print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go - 05/14/2009 RS Aktualisiert, um wirklich freaking große Zahlen zu behandeln, gerade in print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go - Fall print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go :-) print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go - 05/14/2009 RS Aktualisiert, um negative Divisoren zu behandeln. print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go - ********************************************* *************************** print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go deklariere @p_product dezimal (38,19); print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go select @p_product = null; print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go if (@divisor ist nicht null und @divisor <> 0 und @Numerator ist nicht null) print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go wählen Sie @p_product = @Numerator / @divisor; print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go zurück (@p_product) print 'Creating safeDivide Stored Proc ...' go if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide; go create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19)) returns decimal(38,19) begin -- ************************************************************************** -- Procedure: safeDivide() -- Author: Ron Savage, Central, ex: 1282 -- Date: 06/22/2004 -- -- Description: -- This function divides the first argument by the second argument after -- checking for NULL or 0 divisors to avoid "divide by zero" errors. -- Change History: -- -- Date Init. Description -- 05/14/2009 RS Updated to handle really freaking big numbers, just in -- case. :-) -- 05/14/2009 RS Updated to handle negative divisors. -- ************************************************************************** declare @p_product decimal(38,19); select @p_product = null; if ( @divisor is not null and @divisor <> 0 and @Numerator is not null ) select @p_product = @Numerator / @divisor; return(@p_product) end go 
  1. Fügen Sie eine CHECK-Einschränkung hinzu, die den Divisor zwingt, ungleich Null zu sein
  2. Fügen Sie dem Formular einen Validator hinzu, damit der Benutzer keine Nullwerte in dieses Feld eingeben kann.

Für Update-SQLs:

 update Table1 set Col1 = Col2 / ISNULL(NULLIF(Col3,0),1) 

Es gibt keine magische globale Einstellung 'Turn Division durch 0 exceptionn aus'. Die Operation muss zu casting, da die mathematische Bedeutung von x / 0 von der NULL-Bedeutung verschieden ist, also kann es NULL nicht zurückgeben. Ich nehme an, Sie kümmern sich um die offensichtliche und Ihre Fragen haben Bedingungen, die die Aufzeichnungen mit dem 0 Divisor zu beseitigen und nie bewerten die Division. Die üblichen 'gotcha' ist, als die meisten Entwickler erwarten, dass SQL sich wie prozedurale Sprachen verhalten und logischen Operator-Kurzschluss anbieten, aber es tut NICHT . Ich empfehle Ihnen, diesen Artikel zu lesen: http://www.sqlmag.com/Articles/ArticleID/9148/pg/2/2.html

Filtern Sie data aus, indem Sie eine where-Klausel verwenden, damit Sie keine 0-Werte erhalten.

Hier ist eine Situation, wo man sich durch Null teilen kann. Die Geschäftsregel ist, dass zu berechnen Inventar Wendungen, nehmen Sie die Kosten für Waren verkauft für einen timeraum, annualisieren sie. Nachdem Sie die annualisierte Zahl haben, teilen Sie sich durch das durchschnittliche Inventar für den timeraum.

Ich bin auf der Berechnung der Anzahl der Inventar-Turns, die in einem timeraum von drei Monaten auftreten. Ich habe berechnet, dass ich Kosten der Waren verkauft während der drei Monate timeraum von $ 1.000. Die jährliche Umsatzrate beträgt $ 4.000 ($ 1.000 / 3) * 12. Das Anfangsinventar ist 0. Das Endeinventar ist 0. Mein durchschnittliches Inventar ist jetzt 0. Ich habe Verkäufe von $ 4000 pro Jahr und kein Inventar. Dies ergibt eine unendliche Anzahl von Windungen. Dies bedeutet, dass alle meine Inventar wird umgewandelt und von Kunden gekauft.

Dies ist eine Geschäftsregel für die Berechnung von Inventarwindungen.

 CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real) RETURNS Real AS /* Purpose: Handle Division by Zero errors Description: User Defined Scalar Function Parameter(s): @Numerator and @Denominator Test it: SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results UNION ALL SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0) UNION ALL SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16) UNION ALL SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL) UNION ALL SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL) UNION ALL SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0) UNION ALL SELECT '16 / 4', dbo.fn_CORP_Divide(16,4) UNION ALL SELECT '16 / 3', dbo.fn_CORP_Divide(16,3) */ BEGIN RETURN CASE WHEN @Denominator = 0 THEN NULL ELSE @Numerator / @Denominator END END GO CREATE FUNCTION dbo.Divide (@Numerator Real, @Denominator Real) CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real) RETURNS Real AS /* Purpose: Handle Division by Zero errors Description: User Defined Scalar Function Parameter(s): @Numerator and @Denominator Test it: SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results UNION ALL SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0) UNION ALL SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16) UNION ALL SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL) UNION ALL SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL) UNION ALL SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0) UNION ALL SELECT '16 / 4', dbo.fn_CORP_Divide(16,4) UNION ALL SELECT '16 / 3', dbo.fn_CORP_Divide(16,3) */ BEGIN RETURN CASE WHEN @Denominator = 0 THEN NULL ELSE @Numerator / @Denominator END END GO RETURNS Real AS CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real) RETURNS Real AS /* Purpose: Handle Division by Zero errors Description: User Defined Scalar Function Parameter(s): @Numerator and @Denominator Test it: SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results UNION ALL SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0) UNION ALL SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16) UNION ALL SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL) UNION ALL SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL) UNION ALL SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0) UNION ALL SELECT '16 / 4', dbo.fn_CORP_Divide(16,4) UNION ALL SELECT '16 / 3', dbo.fn_CORP_Divide(16,3) */ BEGIN RETURN CASE WHEN @Denominator = 0 THEN NULL ELSE @Numerator / @Denominator END END GO / * CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real) RETURNS Real AS /* Purpose: Handle Division by Zero errors Description: User Defined Scalar Function Parameter(s): @Numerator and @Denominator Test it: SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results UNION ALL SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0) UNION ALL SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16) UNION ALL SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL) UNION ALL SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL) UNION ALL SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0) UNION ALL SELECT '16 / 4', dbo.fn_CORP_Divide(16,4) UNION ALL SELECT '16 / 3', dbo.fn_CORP_Divide(16,3) */ BEGIN RETURN CASE WHEN @Denominator = 0 THEN NULL ELSE @Numerator / @Denominator END END GO Zweck: Handle Division durch Null Fehler CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real) RETURNS Real AS /* Purpose: Handle Division by Zero errors Description: User Defined Scalar Function Parameter(s): @Numerator and @Denominator Test it: SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results UNION ALL SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0) UNION ALL SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16) UNION ALL SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL) UNION ALL SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL) UNION ALL SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0) UNION ALL SELECT '16 / 4', dbo.fn_CORP_Divide(16,4) UNION ALL SELECT '16 / 3', dbo.fn_CORP_Divide(16,3) */ BEGIN RETURN CASE WHEN @Denominator = 0 THEN NULL ELSE @Numerator / @Denominator END END GO Beschreibung: Benutzerdefinierte Skalarfunktion CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real) RETURNS Real AS /* Purpose: Handle Division by Zero errors Description: User Defined Scalar Function Parameter(s): @Numerator and @Denominator Test it: SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results UNION ALL SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0) UNION ALL SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16) UNION ALL SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL) UNION ALL SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL) UNION ALL SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0) UNION ALL SELECT '16 / 4', dbo.fn_CORP_Divide(16,4) UNION ALL SELECT '16 / 3', dbo.fn_CORP_Divide(16,3) */ BEGIN RETURN CASE WHEN @Denominator = 0 THEN NULL ELSE @Numerator / @Denominator END END GO Parameter (s): @Numerator und @Denominator CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real) RETURNS Real AS /* Purpose: Handle Division by Zero errors Description: User Defined Scalar Function Parameter(s): @Numerator and @Denominator Test it: SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results UNION ALL SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0) UNION ALL SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16) UNION ALL SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL) UNION ALL SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL) UNION ALL SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0) UNION ALL SELECT '16 / 4', dbo.fn_CORP_Divide(16,4) UNION ALL SELECT '16 / 3', dbo.fn_CORP_Divide(16,3) */ BEGIN RETURN CASE WHEN @Denominator = 0 THEN NULL ELSE @Numerator / @Denominator END END GO SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide (0,16) Ergebnisse CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real) RETURNS Real AS /* Purpose: Handle Division by Zero errors Description: User Defined Scalar Function Parameter(s): @Numerator and @Denominator Test it: SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results UNION ALL SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0) UNION ALL SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16) UNION ALL SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL) UNION ALL SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL) UNION ALL SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0) UNION ALL SELECT '16 / 4', dbo.fn_CORP_Divide(16,4) UNION ALL SELECT '16 / 3', dbo.fn_CORP_Divide(16,3) */ BEGIN RETURN CASE WHEN @Denominator = 0 THEN NULL ELSE @Numerator / @Denominator END END GO SELECT 'Nenner = 0', dbo.fn_CORP_Divide (16,0) CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real) RETURNS Real AS /* Purpose: Handle Division by Zero errors Description: User Defined Scalar Function Parameter(s): @Numerator and @Denominator Test it: SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results UNION ALL SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0) UNION ALL SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16) UNION ALL SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL) UNION ALL SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL) UNION ALL SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0) UNION ALL SELECT '16 / 4', dbo.fn_CORP_Divide(16,4) UNION ALL SELECT '16 / 3', dbo.fn_CORP_Divide(16,3) */ BEGIN RETURN CASE WHEN @Denominator = 0 THEN NULL ELSE @Numerator / @Denominator END END GO SELECT 'Numerator ist NULL', dbo.fn_CORP_Divide (NULL, 16) CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real) RETURNS Real AS /* Purpose: Handle Division by Zero errors Description: User Defined Scalar Function Parameter(s): @Numerator and @Denominator Test it: SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results UNION ALL SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0) UNION ALL SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16) UNION ALL SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL) UNION ALL SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL) UNION ALL SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0) UNION ALL SELECT '16 / 4', dbo.fn_CORP_Divide(16,4) UNION ALL SELECT '16 / 3', dbo.fn_CORP_Divide(16,3) */ BEGIN RETURN CASE WHEN @Denominator = 0 THEN NULL ELSE @Numerator / @Denominator END END GO SELECT 'Nenner ist NULL', dbo.fn_CORP_Divide (16, NULL) CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real) RETURNS Real AS /* Purpose: Handle Division by Zero errors Description: User Defined Scalar Function Parameter(s): @Numerator and @Denominator Test it: SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results UNION ALL SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0) UNION ALL SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16) UNION ALL SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL) UNION ALL SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL) UNION ALL SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0) UNION ALL SELECT '16 / 4', dbo.fn_CORP_Divide(16,4) UNION ALL SELECT '16 / 3', dbo.fn_CORP_Divide(16,3) */ BEGIN RETURN CASE WHEN @Denominator = 0 THEN NULL ELSE @Numerator / @Denominator END END GO SELECT 'Numerator & Nenner ist NULL', dbo.fn_CORP_Divide (NULL, NULL) CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real) RETURNS Real AS /* Purpose: Handle Division by Zero errors Description: User Defined Scalar Function Parameter(s): @Numerator and @Denominator Test it: SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results UNION ALL SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0) UNION ALL SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16) UNION ALL SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL) UNION ALL SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL) UNION ALL SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0) UNION ALL SELECT '16 / 4', dbo.fn_CORP_Divide(16,4) UNION ALL SELECT '16 / 3', dbo.fn_CORP_Divide(16,3) */ BEGIN RETURN CASE WHEN @Denominator = 0 THEN NULL ELSE @Numerator / @Denominator END END GO SELECT 'Numerator & Nenner = 0', dbo.fn_CORP_Divide (0,0) CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real) RETURNS Real AS /* Purpose: Handle Division by Zero errors Description: User Defined Scalar Function Parameter(s): @Numerator and @Denominator Test it: SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results UNION ALL SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0) UNION ALL SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16) UNION ALL SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL) UNION ALL SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL) UNION ALL SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0) UNION ALL SELECT '16 / 4', dbo.fn_CORP_Divide(16,4) UNION ALL SELECT '16 / 3', dbo.fn_CORP_Divide(16,3) */ BEGIN RETURN CASE WHEN @Denominator = 0 THEN NULL ELSE @Numerator / @Denominator END END GO SELECT '16 / 4 ', dbo.fn_CORP_Divide (16,4) CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real) RETURNS Real AS /* Purpose: Handle Division by Zero errors Description: User Defined Scalar Function Parameter(s): @Numerator and @Denominator Test it: SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results UNION ALL SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0) UNION ALL SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16) UNION ALL SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL) UNION ALL SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL) UNION ALL SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0) UNION ALL SELECT '16 / 4', dbo.fn_CORP_Divide(16,4) UNION ALL SELECT '16 / 3', dbo.fn_CORP_Divide(16,3) */ BEGIN RETURN CASE WHEN @Denominator = 0 THEN NULL ELSE @Numerator / @Denominator END END GO SELECT '16 / 3 ', dbo.fn_CORP_Divide (16,3) CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real) RETURNS Real AS /* Purpose: Handle Division by Zero errors Description: User Defined Scalar Function Parameter(s): @Numerator and @Denominator Test it: SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results UNION ALL SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0) UNION ALL SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16) UNION ALL SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL) UNION ALL SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL) UNION ALL SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0) UNION ALL SELECT '16 / 4', dbo.fn_CORP_Divide(16,4) UNION ALL SELECT '16 / 3', dbo.fn_CORP_Divide(16,3) */ BEGIN RETURN CASE WHEN @Denominator = 0 THEN NULL ELSE @Numerator / @Denominator END END GO * / CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real) RETURNS Real AS /* Purpose: Handle Division by Zero errors Description: User Defined Scalar Function Parameter(s): @Numerator and @Denominator Test it: SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results UNION ALL SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0) UNION ALL SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16) UNION ALL SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL) UNION ALL SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL) UNION ALL SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0) UNION ALL SELECT '16 / 4', dbo.fn_CORP_Divide(16,4) UNION ALL SELECT '16 / 3', dbo.fn_CORP_Divide(16,3) */ BEGIN RETURN CASE WHEN @Denominator = 0 THEN NULL ELSE @Numerator / @Denominator END END GO CASE WHEN @Denominator = 0 DANN CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real) RETURNS Real AS /* Purpose: Handle Division by Zero errors Description: User Defined Scalar Function Parameter(s): @Numerator and @Denominator Test it: SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results UNION ALL SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0) UNION ALL SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16) UNION ALL SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL) UNION ALL SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL) UNION ALL SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0) UNION ALL SELECT '16 / 4', dbo.fn_CORP_Divide(16,4) UNION ALL SELECT '16 / 3', dbo.fn_CORP_Divide(16,3) */ BEGIN RETURN CASE WHEN @Denominator = 0 THEN NULL ELSE @Numerator / @Denominator END END GO 

Benutze NULLIF(exp,0) aber auf diese Weise – NULLIF(ISNULL(exp,0),0)

NULLIF(exp,0) bricht, wenn exp null aber NULLIF(ISNULL(exp,0),0) wird nicht brechen

Sie können den Fehler entsprechend behandeln, wenn er sich zurück zum anrufenden Programm ausbreitet (oder ignorieren Sie, wenn es das ist, was Sie wollen). In C # irgendwelche Fehler, die in SQL auftreten, casting eine exception, die ich fangen kann und dann in meinem Code behandeln, wie jeder andere Fehler.

Ich stimme mit Beska darin überein, dass du den Fehler nicht verbergen willst. Sie dürfen nicht mit einem Atomreaktor umgehen, aber versteckende Fehler im Allgemeinen ist schlechte Programmierpraxis. Dies ist einer der Gründe, warum modernste Programmiersprachen eine strukturierte exceptionbehandlung implementieren, um den tatsächlichen Rückgabewert mit einem Fehler- / Statuscode zu entkoppeln. Das ist besonders wahr, wenn du Mathe machst. Das größte Problem ist, dass man nicht unterscheiden kann zwischen einem korrekt berechneten 0 zurückgegeben oder ein 0 als Ergebnis eines Fehlers. Stattdessen ist jeder zurückgegebene Wert der berechnete Wert und wenn etwas schief geht, wird eine exception ausgetriggers. Dies hängt natürlich davon ab, wie Sie auf die database zugreifen und welche Sprache Sie verwenden, aber Sie sollten immer in der Lage sein, eine Fehlermeldung zu erhalten, mit der Sie umgehen können.

 try { Database.ComputePercentage(); } catch (SqlException e) { // now you can handle the exception or at least log that the exception was thrown if you choose not to handle it // Exception Details: System.Data.SqlClient.SqlException: Divide by zero error encountered. } { try { Database.ComputePercentage(); } catch (SqlException e) { // now you can handle the exception or at least log that the exception was thrown if you choose not to handle it // Exception Details: System.Data.SqlClient.SqlException: Divide by zero error encountered. } Database.ComputePercentage (); try { Database.ComputePercentage(); } catch (SqlException e) { // now you can handle the exception or at least log that the exception was thrown if you choose not to handle it // Exception Details: System.Data.SqlClient.SqlException: Divide by zero error encountered. } } try { Database.ComputePercentage(); } catch (SqlException e) { // now you can handle the exception or at least log that the exception was thrown if you choose not to handle it // Exception Details: System.Data.SqlClient.SqlException: Divide by zero error encountered. } catch (SqlException e) try { Database.ComputePercentage(); } catch (SqlException e) { // now you can handle the exception or at least log that the exception was thrown if you choose not to handle it // Exception Details: System.Data.SqlClient.SqlException: Divide by zero error encountered. } { try { Database.ComputePercentage(); } catch (SqlException e) { // now you can handle the exception or at least log that the exception was thrown if you choose not to handle it // Exception Details: System.Data.SqlClient.SqlException: Divide by zero error encountered. } // Jetzt können Sie die exception behandeln oder zumindest protokollieren, dass die exception ausgetriggers wurde, wenn Sie sich dafür entscheiden, es nicht zu behandeln try { Database.ComputePercentage(); } catch (SqlException e) { // now you can handle the exception or at least log that the exception was thrown if you choose not to handle it // Exception Details: System.Data.SqlClient.SqlException: Divide by zero error encountered. } // Exception Details: System.Data.SqlClient.SqlException: Divide by Null Fehler aufgetreten. try { Database.ComputePercentage(); } catch (SqlException e) { // now you can handle the exception or at least log that the exception was thrown if you choose not to handle it // Exception Details: System.Data.SqlClient.SqlException: Divide by zero error encountered. }