Comment signaler une erreur à partir d'une fonction définie par l'utilisateur de SQL Server ?

Question

Je suis en train d'écrire une fonction définie par l'utilisateur dans SQL Server 2008. Je sais que les fonctions ne peuvent pas soulever d'erreurs de la manière habituelle - si vous essayez d'inclure l'instruction RAISERROR, SQL renvoie :

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

Mais le fait est que la fonction prend une entrée, qui peut être invalide et, si c'est le cas, il n'y a aucune valeur significative que la fonction peut retourner. Que dois-je faire alors ?

Je pourrais, bien sûr, renvoyer NULL, mais il serait difficile pour tout développeur utilisant la fonction de résoudre ce problème. Je pourrais aussi provoquer une division par zéro ou quelque chose du genre - cela générerait un message d'erreur, mais un message trompeur. Existe-t-il un moyen de faire en sorte que mon propre message d'erreur soit signalé d'une manière ou d'une autre ?

Answer 1

Vous pouvez utiliser CAST pour lancer une erreur significative :

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

Ensuite, Sql Server affichera des informations d'aide :

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

Answer 2

L'astuce habituelle consiste à forcer une division par 0, ce qui déclenche une erreur et interrompt l'instruction en cours qui évalue la fonction. Si le développeur ou la personne chargée de l'assistance technique connaît ce comportement, l'investigation et la résolution du problème sont relativement faciles, car l'erreur de division par 0 est considérée comme le symptôme d'un problème différent et non lié.

Aussi mauvais que cela puisse paraître à tout point de vue, la conception actuelle des fonctions SQL ne permet malheureusement pas de meilleur choix. L'utilisation de RAISERROR devrait absolument être autorisée dans les fonctions.

Answer 3

Suite à la réponse de Vladimir Korolev, l'idiome pour lancer une erreur de manière conditionnelle est le suivant

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 4

RAISEERROR o @@ERROR ne sont pas autorisés dans les UDF. Pouvez-vous transformer l'UDF en une procédure strored ?

Extrait de l'article d'Erland Sommarskog Gestion des erreurs dans SQL Server - un historique :

Les fonctions définies par l'utilisateur sont généralement invoquées dans le cadre d'une instruction SET, SELECT, INSERT, UPDATE ou DELETE. Ce que j'ai constaté, c'est que si une erreur apparaît dans une fonction à valeur de table fonction à valeur de tableau ou dans une fonction l'exécution de la fonction fonction est immédiatement interrompue, et ainsi que l'instruction dont la fonction fait fonction fait partie. L'exécution se poursuit sur la ligne suivante, sauf si l'erreur a interrompu le lot. Dans les deux cas, @@error vaut 0. Il n'y a donc aucun moyen de détecter qu'une erreur s'est produite dans une fonction à partir de T-SQL.

Le problème n'apparaît pas avec inline, puisqu'une fonction fonction en ligne de type tableau est est essentiellement une macro que le processeur colle dans la requête.

Vous pouvez également exécuter des fonctions scalaires avec l'instruction EXEC. Dans ce cas, l'exécution l'exécution continue si une erreur se produit (sauf s'il s'agit d'une erreur d'abandon de lot). @@error est définie et vous pouvez vérifier la valeur de valeur de @@error dans la fonction. Il peut être problématique de communiquer l'erreur à l'appelant.

Answer 5

La première réponse est généralement la meilleure, mais elle ne fonctionne pas pour les fonctions de type tableau en ligne.

MikeTeeVee a donné une solution à ce problème dans son commentaire sur la première réponse, mais elle nécessitait l'utilisation d'une fonction d'agrégation comme MAX, ce qui ne fonctionnait pas bien dans mon cas.

J'ai essayé une solution alternative pour le cas où vous avez besoin d'un tableau en ligne évalué par udf qui renvoie quelque chose comme sélectionner * au lieu d'un agrégat. Un exemple de code résolvant ce cas particulier est ci-dessous. Comme quelqu'un l'a déjà souligné... "JEEZ wotta hack" :) Toute solution meilleure pour ce cas est la bienvenue !

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