webentwicklung-frage-antwort-db.com.de

So melden Sie einen Fehler von einer benutzerdefinierten SQL Server-Funktion

Ich schreibe eine benutzerdefinierte Funktion in SQL Server 2008. Ich weiß, dass Funktionen keine Fehler auf die übliche Weise auslösen können - wenn Sie versuchen, die RAISERROR-Anweisung einzuschließen, gibt SQL Folgendes zurück:

Msg 443, Level 16, State 14, Procedure ..., Line ...
Invalid use of a side-effecting operator 'RAISERROR' within a function.

Tatsache ist jedoch, dass die Funktion Eingaben akzeptiert, die möglicherweise ungültig sind. Ist dies der Fall, gibt es keinen aussagekräftigen Wert, den die Funktion zurückgeben kann. Was mache ich dann

Ich könnte natürlich NULL zurückgeben, aber es wäre für jeden Entwickler, der die Funktion verwendet, schwierig, dies zu beheben. Ich könnte auch eine Division durch Null oder ähnliches verursachen - dies würde eine Fehlermeldung erzeugen, aber eine irreführende. Kann ich irgendwie meine eigene Fehlermeldung melden lassen?

141
EMP

Mit CAST können Sie aussagekräftige Fehler auslösen:

create function dbo.throwError()
returns nvarchar(max)
as
begin
    return cast('Error happened here.' as int);
end

Dann zeigt SQL Server einige Hilfeinformationen an:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Error happened here.' to data type int.
212

Der übliche Trick besteht darin, eine Division durch 0 zu erzwingen. Dies löst einen Fehler aus und unterbricht die aktuelle Anweisung, die die Funktion auswertet. Wenn der Entwickler oder die Support-Person von diesem Verhalten Kenntnis hat, ist die Untersuchung und Behebung des Problems recht einfach, da der Fehler "Division durch 0" als Symptom für ein anderes, nicht damit zusammenhängendes Problem verstanden wird.

So schlecht dies auch immer aussehen mag, leider lässt der Entwurf von SQL-Funktionen im Moment keine bessere Wahl zu. Die Verwendung von RAISERROR sollte in Funktionen unbedingt erlaubt sein.

15
Remus Rusanu

Nach der Antwort von Vladimir Korolev lautet die Redewendung, einen Fehler bedingt zu werfen

CREATE FUNCTION [dbo].[Throw]
(
    @error NVARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
    RETURN CAST(@error AS INT)
END
GO

DECLARE @error NVARCHAR(MAX)
DECLARE @bit BIT

IF `error condition` SET @error = 'My Error'
ELSE SET @error = '0'

SET @bit = [dbo].[Throw](@error)    
7
briantyler

Ich denke, der sauberste Weg ist, einfach zu akzeptieren, dass die Funktion NULL zurückgeben kann, wenn ungültige Argumente übergeben werden. Solange dies eindeutig dokumentiert ist, sollte dies in Ordnung sein?

-- =============================================
-- Author: AM
-- Create date: 03/02/2010
-- Description: Returns the appropriate exchange rate
-- based on the input parameters.
-- If the rate cannot be found, returns NULL
-- (RAISEERROR can't be used in UDFs)
-- =============================================
ALTER FUNCTION [dbo].[GetExchangeRate] 
(
    @CurrencyFrom char(3),
    @CurrencyTo char(3),
    @OnDate date
)
RETURNS decimal(18,4)
AS
BEGIN

  DECLARE @ClosingRate as decimal(18,4)

    SELECT TOP 1
        @ClosingRate=ClosingRate
    FROM
        [FactCurrencyRate]
    WHERE
        [email protected] AND
        [email protected] AND
        DateID=dbo.DateToIntegerKey(@OnDate)

    RETURN @ClosingRate 

END
GO
6
AndyM

RAISEERROR oder @@ERROR sind in UDFs nicht zulässig. Können Sie die UDF in eine gespeicherte Prozedur verwandeln?

Aus dem Artikel von Erland Sommarskog Fehlerbehandlung in SQL Server - ein Hintergrund :

Benutzerdefinierte Funktionen werden normalerweise als Teil einer SET-, SELECT-, INSERT-, UPDATE- oder DELETE-Anweisung aufgerufen. Was ich festgestellt habe, ist, dass, wenn ein Fehler in einer Funktion mit mehreren Anweisungen und Tabellenwerten oder in einer skalaren Funktion auftritt, die Ausführung der Funktion sofort abgebrochen wird, und ebenso die Anweisung, zu der die Funktion gehört. Die Ausführung wird in der nächsten Zeile fortgesetzt, es sei denn, der Fehler hat den Stapel abgebrochen. In beiden Fällen ist @@ error 0. Daher kann nicht festgestellt werden, dass in einer Funktion von T-SQL ein Fehler aufgetreten ist.

Das Problem tritt bei Inline-Tabellenfunktionen nicht auf, da eine Inline-Tabellenwertfunktion im Grunde ein Makro ist, das der Abfrageprozessor in die Abfrage einfügt.

Sie können skalare Funktionen auch mit der EXEC-Anweisung ausführen. In diesem Fall wird die Ausführung fortgesetzt, wenn ein Fehler auftritt (es sei denn, es handelt sich um einen Stapelabbruchfehler). @@ error wird gesetzt und Sie können den Wert von @@ error innerhalb der Funktion überprüfen. Es kann jedoch problematisch sein, dem Anrufer den Fehler mitzuteilen.

5
Mitch Wheat

Die beste Antwort ist im Allgemeinen die beste, funktioniert jedoch nicht für Inline-Tabellenfunktionen.

MikeTeeVee gab in seinem Kommentar zur Top-Antwort eine Lösung dafür an, aber es erforderte die Verwendung einer Aggregatfunktion wie MAX, die unter meinen Umständen nicht gut funktionierte.

Ich habe mit einer alternativen Lösung für den Fall rumgespielt, dass Sie eine Inline-Tabelle mit dem Wert udf benötigen, die stattdessen etwas wie select * zurückgibt eines Aggregats. Beispielcode, der diesen speziellen Fall löst, ist unten. Wie schon jemand darauf hingewiesen hat ... "JEEZ will hacken" :) Ich begrüße jede bessere Lösung für diesen Fall!

create table foo (
    ID nvarchar(255),
    Data nvarchar(255)
)
go

insert into foo (ID, Data) values ('Green Eggs', 'Ham')
go

create function dbo.GetFoo(@aID nvarchar(255)) returns table as return (
    select *, 0 as CausesError from foo where ID = @aID

    --error checking code is embedded within this union
    --when the ID exists, this second selection is empty due to where clause at end
    --when ID doesn't exist, invalid cast with case statement conditionally causes an error
    --case statement is very hack-y, but this was the only way I could get the code to compile
    --for an inline TVF
    --simpler approaches were caught at compile time by SQL Server
    union

    select top 1 *, case
                        when ((select top 1 ID from foo where ID = @aID) = @aID) then 0
                        else 'Error in GetFoo() - ID "' + IsNull(@aID, 'null') + '" does not exist'
                    end
    from foo where (not exists (select ID from foo where ID = @aID))
)
go

--this does not cause an error
select * from dbo.GetFoo('Green Eggs')
go

--this does cause an error
select * from dbo.GetFoo('Yellow Eggs')
go

drop function dbo.GetFoo
go

drop table foo
go
4
davec

Einige Leute fragten nach Fehlern in Tabellenwertfunktionen, da Sie "RETURN [ungültige Besetzung]" nicht verwenden können. Das Zuweisen der ungültigen Besetzung zu einer Variablen funktioniert genauso gut.

CREATE FUNCTION fn()
RETURNS @T TABLE (Col CHAR)  
AS
BEGIN

DECLARE @i INT = CAST('booooom!' AS INT)  

RETURN

END

Das führt zu:

Meldung 245, Ebene 16, Status 1, Zeile 14 Die Konvertierung ist fehlgeschlagen, wenn der varchar-Wert "booooom!" zum Datentyp int.

4
NightShovel

Ich kann unter Davecs Antwort bezüglich der Tabellenwertfunktion keinen Kommentar abgeben, aber meiner bescheidenen Meinung nach ist dies eine einfachere Lösung:

CREATE FUNCTION dbo.ufn_test (@a TINYINT)
RETURNS @returns TABLE(Column1 VARCHAR(10), Value1 TINYINT)
BEGIN
    IF @a>50 -- if @a > 50 - raise an error
    BEGIN
      INSERT INTO @returns (Column1, Value1)
      VALUES('error','@a is bigger than 50!') -- reminder Value1 should be TINYINT
    END

    INSERT INTO @returns (Column1, Value1)
    VALUES('Something',@a)
    RETURN;
END

SELECT Column1, Value1 FROM dbo.ufn_test(1) -- this is okay
SELECT Column1, Value1 FROM dbo.ufn_test(51) -- this will raise an error
2
Michal Zglinski