Jak zgłosić błąd z funkcji zdefiniowanej przez użytkownika programu SQL Server

Piszę funkcję zdefiniowaną przez użytkownika w SQL Server 2008. Wiem, że funkcje nie mogą generować błędów w zwykły sposób - jeśli spróbujesz dołączyć instrukcję RAISERROR SQL zwraca:

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

Ale faktem jest, że funkcja pobiera dane wejściowe, które mogą być nieprawidłowe, a jeśli tak, to nie ma żadnej znaczącej wartości, którą funkcja może zwrócić. Co mam wtedy zrobić?

Mógłbym oczywiście zwrócić wartość NULL, ale każdemu deweloperowi, który użyłby tej funkcji, byłoby trudne do rozwiązania tego problemu. Mógłbym też spowodować dzielenie przez zero lub coś w tym rodzaju - wygenerowałoby to komunikat o błędzie, ale wprowadzający w błąd. Czy jest jakiś sposób, żebym mógł w jakiś sposób zgłosić swój własny komunikat o błędzie?

Możesz użyć CAST, aby zgłosić znaczący błąd:

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

Następnie Sql Server wyświetli kilka informacji pomocy:

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

Zwykłą sztuczką jest wymuszenie dzielenia przez 0. Spowoduje to zgłoszenie błędu i przerwanie bieżącej instrukcji oceniającej funkcję. Jeśli programista lub osoba z pomocy technicznej wie o takim zachowaniu, zbadanie i rozwiązanie problemu jest dość łatwe, ponieważ dzielenie przez błąd 0 jest rozumiane jako objaw innego, niezwiązanego z nim problemu.

Choć wygląda to źle z dowolnego punktu widzenia, niestety projekt funkcji SQL w tej chwili nie pozwala na lepszy wybór. Używanie RAISERROR powinno być absolutnie dozwolone w funkcjach.

Idąc za odpowiedzią Vladimira Koroleva, idiomem warunkowego rzucania błędu jest

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)    

Myślę, że najczystszym sposobem jest po prostu zaakceptowanie, że funkcja może zwrócić wartość NULL, jeśli zostaną przekazane nieprawidłowe argumenty. Jeśli jest to jasno udokumentowane, to powinno być w porządku?

-- =============================================
-- 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
        FromCurrencyCode=@CurrencyFrom AND
        ToCurrencyCode=@CurrencyTo AND
        DateID=dbo.DateToIntegerKey(@OnDate)

    RETURN @ClosingRate 

END
GO

RAISEERRORlub @@ERRORnie są dozwolone w UDF. Czy można przekształcić UDF w procedurę strated?

Z artykułu Erlanda Sommarskoga Obsługa błędów w SQL Server - tło :

Funkcje zdefiniowane przez użytkownika są zwykle wywoływane jako część instrukcji SET, SELECT, INSERT, UPDATE lub DELETE. Odkryłem, że jeśli błąd pojawia się w funkcji z wartościami tabelarycznymi złożonej z wielu instrukcji lub w funkcji skalarnej, wykonanie funkcji jest natychmiast przerywane, podobnie jak instrukcja, której częścią jest funkcja. Wykonywanie jest kontynuowane w następnym wierszu, chyba że błąd przerwał wsad. W obu przypadkach błąd @@ wynosi 0. Dlatego nie ma możliwości wykrycia błędu w funkcji z T-SQL.

Problem nie pojawia się w przypadku wbudowanych funkcji tabelarycznych, ponieważ wbudowana funkcja wartościowana w tabeli jest w zasadzie makrem, które procesor zapytań wkleja do zapytania.

Za pomocą instrukcji EXEC można również wykonywać funkcje skalarne. W takim przypadku wykonanie jest kontynuowane, jeśli wystąpi błąd (chyba że jest to błąd przerywania partii). @@ błąd jest ustawiony i możesz sprawdzić wartość błędu @@ w funkcji. Jednak przekazanie błędu do dzwoniącego może być problematyczne.

Najlepsza odpowiedź jest ogólnie najlepsza, ale nie działa w przypadku funkcji wartościowanych w tabeli wbudowanej.

MikeTeeVee podał rozwiązanie tego problemu w swoim komentarzu do górnej odpowiedzi, ale wymagało to użycia funkcji agregującej, takiej jak MAX, która nie działała dobrze w moich okolicznościach.

Bawiłem się alternatywnym rozwiązaniem w przypadku, gdy potrzebujesz pliku udf o wartości tabeli wbudowanej, który zwraca coś w rodzaju select * zamiast agregatu. Przykładowy kod rozwiązujący ten konkretny przypadek znajduje się poniżej. Jak już ktoś wskazał ... "JEEZ wotta hack" :) Z zadowoleniem przyjmuję lepsze rozwiązanie w tej sprawie!

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

Kilku ludzi pytało o zgłaszanie błędów w funkcjach z wartościami tabelarycznymi, ponieważ nie można używać rzeczy typu „ RETURN [nieprawidłowe rzutowanie] ”. Przypisanie nieprawidłowego rzutowania do zmiennej działa równie dobrze.

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

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

RETURN

END

To skutkuje:

Msg 245, poziom 16, stan 1, wiersz 14 Konwersja nie powiodła się podczas konwersji wartości varchar „booooom!” do typu danych int.

Nie mogę komentować odpowiedzi davec na temat funkcji wycenianej w tabeli, ale moim skromnym zdaniem jest to łatwiejsze rozwiązanie:

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

Jednym ze sposobów (hack) jest posiadanie funkcji / procedury składowanej, która wykonuje nieprawidłową akcję. Na przykład następujący pseudo SQL

create procedure throw_error ( in err_msg varchar(255))
begin
insert into tbl_throw_error (id, msg) values (null, err_msg);
insert into tbl_throw_error (id, msg) values (null, err_msg);
end;

Gdzie w tabeli tbl_throw_error istnieje unikalne ograniczenie w kolumnie err_msg. Efektem ubocznym tego (przynajmniej w MySQL) jest to, że wartość err_msg jest używana jako opis wyjątku, gdy wraca on do obiektu wyjątku na poziomie aplikacji.

Nie wiem, czy możesz zrobić coś podobnego z SQL Server, ale warto spróbować.