Как сообщить об ошибке из пользовательской функции SQL Server

Ответ 1

Вы можете использовать CAST для создания значимой ошибки:

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

Затем сервер Sql отобразит некоторую справочную информацию:

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

Ответ 2

Обычный трюк состоит в том, чтобы заставить деление на 0. Это вызовет ошибку и прервет текущую инструкцию, которая оценивает функцию. Если разработчик или специалист поддержки знает об этом поведении, исследование и устранение неполадок довольно легко, так как ошибка деления на 0 понимается как симптом другой, не связанной с этим проблемы.

Так же плохо, как это выглядит с любой точки зрения, к сожалению, дизайн SQL-функций на данный момент не позволяет лучшего выбора. Использование RAISERROR должно быть абсолютно разрешено в функциях.

Ответ 3

После ответа Владимира Королева, идиома условно выдает ошибку

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)    

Ответ 4

RAISEERROR или @@ERROR не разрешены в UDF. Можете ли вы включить UDF в обработанную процедуру?

Из статьи Эрланда Соммарскога Обработка ошибок в SQL Server - справочная информация:

Пользовательские функции обычно вызывается как часть SET, SELECT, INSERT, UPDATE или DELETE. Я обнаружил, что если ошибка появляется в мультирекламе табличная функция или скаляр функции, выполнение функция прерывается немедленно, и так и утверждение, что функция часть. Выполнение продолжается следующей строке, если ошибка не прервана пакет. В любом случае ошибка @@ 0. Таким образом, невозможно обнаружить, что в функции возникла ошибка от T-SQL.

Проблема не возникает с встроенных табличных функций, поскольку встроенная табличная функция в основном макрос, который запрашивает процессор вставляет в запрос.

Вы также можете выполнять скалярные функции с выражением EXEC. В этом случае, выполнение продолжается, если возникает ошибка (если это ошибка прерывания партии). @@установлена ​​ошибка, и вы можете проверить значение ошибки @@в функции. Это может быть проблематично для общения ошибка для вызывающего абонента.

Ответ 5

Я думаю, что самый чистый способ - просто принять, что функция может возвращать NULL, если переданы недопустимые аргументы. До тех пор, пока это четко документировано, это должно быть хорошо?

-- =============================================
-- 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

Верхний ответ, как правило, лучше всего, но не работает для встроенных функций, связанных с таблицей.

MikeTeeVee дал решение для этого в своем комментарии к верхнему ответу, но для этого потребовалось использование агрегатной функции, такой как MAX, что не помогло мне в моих обстоятельствах.

Я столкнулся с альтернативным решением для случая, когда вам нужна встроенная таблица с оценкой udf, которая возвращает нечто вроде select * вместо агрегата. Пример кода, разрешающий этот конкретный случай, приведен ниже. Как уже указывал кто-то... "JEEZ wotta hack" :) Я приветствую любое лучшее решение для этого случая!

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

Ответ 7

Я не могу комментировать ответ davec относительно функции, связанной с таблицей, но по моему скромному мнению это более простое решение:

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

Ответ 8

Несколько человек спрашивали о повышении ошибок в табличных значениях, поскольку вы не можете использовать " RETURN [invalid cast]. Присвоение неверного нажатия переменной также работает.

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

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

RETURN

END

Это приводит к:

Msg 245, уровень 16, состояние 1, строка 14 Ошибка конверсии при преобразовании значения varchar 'booooom!' к типу данных int.

Ответ 9

Один из способов (взломать) - иметь функцию/хранимую процедуру, которая выполняет недопустимое действие. Например, следующий псевдо 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;

Где в таблице tbl_throw_error существует уникальное ограничение на столбец err_msg. Побочным эффектом этого (по крайней мере, для MySQL) является то, что значение err_msg используется как описание исключения, когда оно возвращается в объект исключения уровня приложения.

Я не знаю, можете ли вы сделать что-то подобное с SQL Server, но стоит сделать снимок.