sqlstate - login failed for user ''sa''

Cómo informar un error de una función definida por el usuario de SQL Server

Question

Estoy escribiendo una función definida por el usuario en SQL Server 2008. Sé que las funciones no pueden generar errores de la forma habitual: si intenta incluir la instrucción RAISERROR, SQL devuelve:

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

Pero el hecho es que la función toma alguna entrada, que puede ser inválida y, si lo es, no hay un valor significativo que la función pueda devolver. ¿Qué hago entonces?

Podría, por supuesto, devolver NULL, pero sería difícil para cualquier desarrollador que usa la función solucionar este problema. También podría causar una división por cero o algo así - esto generaría un mensaje de error, pero uno engañoso. ¿Hay alguna manera en que pueda tener mi propio mensaje de error informado de alguna manera?

Answer 1

Algunas personas preguntaban sobre la generación de errores en las funciones con valores de tabla, ya que no se puede usar el tipo de cosas " RETURN [inválido] ". Asignar el cast inválido a una variable funciona igual de bien.

CREATE FUNCTION fn()
RETURNS @T TABLE (Col CHAR)
AS
BEGIN
DECLARE @i INT = CAST(''booooom!'' AS INT)
RETURN
END

Esto resulta en:

Conversión de Msg 245, nivel 16, estado 1, línea 14 al convertir el valor varchar ''booooom!'' al tipo de datos int.

Answer 2

Creo que la manera más limpia es simplemente aceptar que la función puede devolver NULL si se pasan argumentos no válidos. ¿Siempre y cuando esto esté claramente documentado, entonces esto debería estar bien?

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

Answer 3

El truco habitual es forzar una división por 0. Esto generará un error e interrumpirá la declaración actual que está evaluando la función. Si el desarrollador o la persona de soporte conocen este comportamiento, es bastante fácil investigar y resolver el problema, ya que el error de división por 0 se entiende como un síntoma de un problema diferente y no relacionado.

Por malo que esto parezca desde cualquier punto de vista, lamentablemente el diseño de las funciones de SQL en este momento no permite una mejor opción. El uso de RAISERROR debería estar permitido en funciones.

Answer 4

La respuesta principal es generalmente la mejor, pero no funciona para las funciones con valores de tabla en línea.

MikeTeeVee dio una solución para esto en su comentario sobre la respuesta principal, pero requería el uso de una función agregada como MAX, que no funcionó bien para mi circunstancia.

Me equivoqué con una solución alternativa para el caso en el que necesitas una tabla en línea valorada como udf que devuelve algo así como select * en lugar de un agregado. El código de ejemplo para resolver este caso particular está a continuación. Como alguien ya ha señalado ... "JEEZ wotta hack" :) ¡Doy la bienvenida a cualquier solución mejor para este caso!

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

Answer 5

No puedo comentar bajo la respuesta de davec con respecto a la función de tabla valorada, pero en mi humilde opinión esta es la solución más fácil:

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

Answer 6

Puede usar CAST para arrojar un error significativo:

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

Entonces Sql Server mostrará información de ayuda:

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

Answer 7

Siguiendo con la respuesta de Vladimir Korolev, el modismo para arrojar condicionalmente un error es

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)

Answer 8

Una forma (un truco) es tener una función / procedimiento almacenado que realiza una acción no válida. Por ejemplo, el siguiente 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;

Donde en la tabla tbl_throw_error, hay una restricción única en la columna err_msg. Un efecto secundario de esto (al menos en MySQL) es que el valor de err_msg se usa como la descripción de la excepción cuando vuelve a subir al objeto de excepción de nivel de aplicación.

No sé si puedes hacer algo similar con SQL Server, pero vale la pena intentarlo.

Answer 9

RAISEERROR o @@ERROR no están permitidos en UDF. ¿Puedes convertir el UDF en un procedimiento estropeado?

Del artículo de Erland Sommarskog Error Handling en SQL Server - a Background :

Las funciones definidas por el usuario generalmente se invocan como parte de una instrucción SET, SELECT, INSERT, UPDATE o DELETE. Lo que he encontrado es que si aparece un error en una función con múltiples valores de tabla o en una función escalar, la ejecución de la función se cancela inmediatamente, y también lo hace la declaración de la cual es parte. La ejecución continúa en la línea siguiente, a menos que el error anule el lote. En cualquier caso, @@ error es 0. Por lo tanto, no hay manera de detectar que se haya producido un error en una función de T-SQL.

El problema no aparece con las funciones de tabla en línea, ya que una función en línea con valores de tabla es básicamente una macro que el procesador de consultas pega en la consulta.

También puede ejecutar funciones escalares con la instrucción EXEC. En este caso, la ejecución continúa si ocurre un error (a menos que sea un error de aborto de lotes). @@ error está establecido, y puede verificar el valor de @@ error dentro de la función. Sin embargo, puede ser problemático comunicar el error a la persona que llama.