SQL Server 사용자 정의 함수에서 오류를보고하는 방법

SQL Server 사용자 정의 함수에서 오류를보고하는 방법

---

SQL Server 2008에서 사용자 정의 함수를 작성하고 있습니다. RAISERROR 문을 포함하려고하면 함수가 일반적인 방법으로 오류를 발생시킬 수 없다는 것을 알고 있습니다. SQL이 반환합니다.

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

그러나 실제로는 함수가 일부 입력을 가져 와서 유효하지 않을 수 있으며, 유효하지 않은 경우 함수가 리턴 할 수있는 의미있는 값이 없습니다. 그러면 어떻게해야합니까?

물론 NULL을 반환 할 수는 있지만 기능을 사용하는 개발자는이 문제를 해결하기가 어려울 수 있습니다. 또한 0으로 나누거나 그와 비슷한 것을 유발할 수 있습니다. 이는 오류 메시지를 생성하지만 오해의 소지가 있습니다. 어떻게 든 내 자신의 오류 메시지를보고 할 수있는 방법이 있습니까?

---

CAST를 사용하여 의미있는 오류를 발생시킬 수 있습니다.

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

그런 다음 Sql Server는 몇 가지 도움말 정보를 표시합니다.

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

---

일반적인 트릭은 0으로 나누기를 강제하는 것입니다. 이렇게하면 오류가 발생하고 함수를 평가하는 현재 명령문이 중단됩니다. 개발자 또는 지원 담당자가이 동작에 대해 알고 있으면 0으로 나누기 오류가 관련이없는 다른 문제의 증상으로 이해되므로 문제를 조사하고 해결하는 것이 매우 쉽습니다.

불행히도 현재 SQL 함수 디자인은 더 나은 선택을 허용하지 않습니다. RAISERROR 사용은 기능에서 절대적으로 허용되어야합니다.

---

Vladimir Korolev의 답변에 따르면 조건부로 오류를 던지는 관용구는 다음과 같습니다.

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)    

---

가장 깨끗한 방법은 잘못된 인수가 전달되면 함수가 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
        FromCurrencyCode=@CurrencyFrom AND
        ToCurrencyCode=@CurrencyTo AND
        DateID=dbo.DateToIntegerKey(@OnDate)

    RETURN @ClosingRate 

END
GO

---

RAISEERROR or @@ERROR are not allowed in UDFs. Can you turn the UDF into a strored procedure?

From Erland Sommarskog's article Error Handling in SQL Server – a Background:

User-defined functions are usually invoked as part of a SET, SELECT, INSERT, UPDATE or DELETE statement. What I have found is that if an error appears in a multi-statement table-valued function or in a scalar function, the execution of the function is aborted immediately, and so is the statement the function is part of. Execution continues on the next line, unless the error aborted the batch. In either case, @@error is 0. Thus, there is no way to detect that an error occurred in a function from T-SQL.

The problem does not appear with inline table-functions, since an inline table-valued function is basically a macro that the query processor pastes into the query.

You can also execute scalar functions with the EXEC statement. In this case, execution continues if an error occurs (unless it is a batch-aborting error). @@error is set, and you can check the value of @@error within the function. It can be problematic to communicate the error to the caller though.

---

The top answer is generally best, but does not work for inline table valued functions.

MikeTeeVee gave a solution for this in his comment on the top answer, but it required use of an aggregate function like MAX, which did not work well for my circumstance.

I messed around with an alternate solution for the case where you need an inline table valued udf that returns something like select * instead of an aggregate. Sample code solving this particular case is below. As someone has already pointed out... "JEEZ wotta hack" :) I welcome any better solution for this case!

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

---

A few folks were asking about raising errors in Table-Valued functions, since you can't use "RETURN [invalid cast]" sort of things. Assigning the invalid cast to a variable works just as well.

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

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

RETURN

END

This results in:

Msg 245, Level 16, State 1, Line 14 Conversion failed when converting the varchar value 'booooom!' to data type int.

---

I can't comment under davec's answer regarding table valued function, but in my humble opinion this is easier solution:

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

---

One way (a hack) is to have a function/stored procedure that performs an invalid action. For example, the following 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;

Where on the table tbl_throw_error, there is a unique constraint on the column err_msg. A side-effect of this (at least on MySQL), is that the value of err_msg is used as the description of the exception when it gets back up into the application level exception object.

I don't know if you can do something similar with SQL Server, but worth a shot.

참고URL : https://stackoverflow.com/questions/1485034/how-to-report-an-error-from-a-sql-server-user-defined-function