Try-Catch en Fonction Définie par l'Utilisateur?

Je suis en train d'écrire une fonction pour convertir une chaîne de caractères qui est soit un guid ou d'un projet de code associé à ce guid dans le guid:

CREATE FUNCTION fn_user_GetProjectID 
(
    @Project nvarchar(50)
)
RETURNS uniqueidentifier
AS
BEGIN

    declare @ProjectID uniqueidentifier

    BEGIN TRY
        set @ProjectID = cast(@Project as uniqueidentifier)
    END TRY
    BEGIN CATCH
        set @ProjectID = null
    END CATCH

    if(@ProjectID is null)
    BEGIN
        select  @ProjectID = ProjectID from Project where projectcode = @Project
    END

    return @ProjectID

END

Cela fonctionne bien si le code ci-dessus est incorporé dans mes Procédures Stockées, mais je voudrais faire une fonction en dehors de ça alors que je suis à SEC.

Lorsque j'essaie de créer la Fonction, j'obtiens une erreur comme ceci:

Msg 443, Level 16, State 14, Procedure fn_user_GetProjectID, Line 16
Invalid use of side-effecting or time-dependent operator in 'BEGIN TRY' within a function.

Quelqu'un a une idée de comment je peux contourner cette erreur?

Edit: je sais que je ne peux pas utiliser Try-Catch dans une Fonction, je suppose que des questions simples, est-il un moyen de faire un casting qui va juste retourner la valeur NULL si la conversion échoue, au lieu d'une erreur?

Est l'aide de SQLCLR une option ? Si oui, écrire votre propre fonction SQLCLR serait de travailler.
SQLCLR est certainement pas une option. Serait tellement plus facile, oui. J'ai fini par utiliser une combinaison de ma solution et 8 ko.

OriginalL'auteur Moose | 2010-07-08

tsql user-defined-functions

2

De MSDN:

Une colonne ou d'une variable locale de
type de données uniqueidentifier peut être
initialisé à une valeur de l'
une des manières suivantes:

À l'aide de la fonction NEWID.

Par la conversion d'une chaîne de caractères constante
dans la forme
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx,
dans laquelle chaque x est un chiffre hexadécimal
dans la gamme de 0 à 9 ou de a-f.

Par exemple,
6F9619FF-8B86-D011-B42D-00C04FC964FF
est valide valeur uniqueidentifier.

Vous pouvez utiliser le pattern matching pour vérifier la chaîne. Notez que cela ne fonctionne pas pour un codage spécifique qui réduit la taille de l'GUID:
```
declare @Project nvarchar(50) 

declare @ProjectID uniqueidentifier 
declare @HexPattern nvarchar(268) 

set @HexPattern =  
    '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]' +  
    '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]' +  
    '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]' +  
    '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]' 

/* Take into account GUID can have curly-brackets or be missing dashes */
/* Note: this will not work for GUIDs that have been specially encoded */
set @Project = '{' + CAST(NEWID() AS VARCHAR(36)) + '}'

select @Project

set @Project = REPLACE(REPLACE(REPLACE(@Project,'{',''),'}',''),'-','')

/* Cast as uniqueid if pattern matches, otherwise return null */ 
if @Project LIKE @HexPattern 
  select @ProjectID = CAST(
         SUBSTRING(@Project,1,8) + '-' + 
         SUBSTRING(@Project,9,4) + '-' + 
         SUBSTRING(@Project,13,4) + '-' + 
         SUBSTRING(@Project,17,4) + '-' + 
         SUBSTRING(@Project,21,LEN(@Project)-20)
         AS uniqueidentifier) 

select @ProjectID
```
Cette réalité ne prendre en compte que uniqueidentifiers peut également être entouré par des {}, et ils n'ont même pas les tirets. C'est un peu plus compact que ma version mais il échoue dans certaines situations. Je prendrais les tirets de votre @Hexpattern, puis avant j'ai fait le test, en bande les tirets et les accolades.
Bon...je ne pensais pas à l'extérieur de SQL Server.
Modifié le code pour gérer les accolades et des tirets manquants...
Vous n'êtes pas obligé de mettre les tirets avant de faire le casting soit, mais vous avez une nouvelle approche avec le motif.. c'est beaucoup plus propre que ma boucle. Je vais vous donner celui-ci.

OriginalL'auteur 8kb
3

Apparemment, vous ne pouvez pas utiliser TRY-CATCH dans un fichier UDF.

Selon cette de rapport de bug page pour SQL Server:

La documentation en Ligne de documents de ce comportement,
dans la rubrique "CRÉER la FONCTION
(Transact-SQL)":"
les déclarations sont valables en fonction:
[...] De contrôle de Flux consolidés
à l'exception des instructions TRY...CATCH. [...]"

Mais ils étaient en train de donner de l'espoir pour l'avenir en 2006:

Cependant, c'est une limitation sévère
qui devrait être supprimée dans une future
la libération. Vous devriez poster une suggestion
à cet égard, et je vais
de tout coeur voter pour elle.

Eh bien, oui, je sais que je ne peux pas utiliser Try-Catch, c'est pourquoi j'ai demandé à des alternatives. Fosco la méthode de travail en tant que bien, mais je ne veux pas l'erreur si la conversion échoue.

OriginalL'auteur DOK
0

Est pas sûr, mais pourquoi ne pas retourner... au premier coup d'œil je simplifierait les choses comme ceci:
```
select @ProjectID = 
   ISNULL((select ProjectID from Project where 
           projectcode = @Project)
     ,(cast @Project as uniqueidentifier))
```
Si ce n'est pas de fournir suffisamment d'erreur de manipulation, je suis sûr qu'il ya une meilleure façon de pré-vérifier que les acteurs peuvent travailler sans l'aide de try/catch...

Ça ne résout pas le problème de la distribution erroring arrière si @de Projet n'est pas vraiment un code, mais pas un type uniqueidentifier. et, il met de la lenteur de la charrue avant les rapides du cheval. Beaucoup plus rapide pour vérifier si il est de type uniqueidentifier d'abord, que d'essayer d'aller le chercher dans la table.
Comme je l'ai dit, je suis sûr qu'il ya une meilleure façon de valider que le casting sera fonction. Il doit être entièrement numérique, droit? Aussi, est la requête vraiment lent?...
ouais, j'ai plusieurs endroits où je suis en train de faire cela, on est avec plusieurs millions de lignes. le projet sera probablement seulement une couple de cent.

OriginalL'auteur Fosco

Ma force brute de la méthode était de créer mon propre ToGuid() fonction qui vérifie qu'il peut être converti à un GUID premiers, sinon, elle renvoie la valeur null. Il peut ne pas être très rapide, mais il fait le travail, et c'est probablement plus rapide pour convertir le guid si c'en est un que d'essayer de le chercher dans la table. EDIT: je voulais donner du crédit à ce blog, où j'ai obtenu mon code de cette fonction: http://jesschadwick.blogspot.com/2007/11/safe-handling-of-uniqueidentifier-in.html

CREATE FUNCTION [dbo].[ToGuid]
(
@input NVARCHAR(MAX)
)
RETURNS uniqueidentifier
AS
BEGIN 
DECLARE @isValidGuid BIT; 
DECLARE @temp NVARCHAR(MAX); 
SET @isValidGuid = 1; 
SET @temp = UPPER(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@input, '-', ''), '{', ''), '}', '')))); 
IF(@temp IS NOT NULL AND LEN(@temp) = 32) 
BEGIN  
DECLARE @index INT;  
SET @index = 1
WHILE (@index <= 32)  
BEGIN   
IF (SUBSTRING(@temp, @index, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '0', 'A', 'B', 'C', 'D', 'E', 'F'))    
BEGIN
SET @index = @index + 1
END
ELSE    
BEGIN
SET @isValidGuid = 0
BREAK;     
END
END    
END
ELSE
BEGIN
SET @isValidGuid = 0
END  
DECLARE @ret UNIQUEIDENTIFIER
IF(@isValidGuid = 1) 
set @ret = cast(@input AS UNIQUEIDENTIFIER)
ELSE
set @ret = NULL
RETURN @ret
END

Je suis toujours très intéressé si il y a une meilleure réponse que celle-là.

OriginalL'auteur Moose

Vérifier si @de Projet est un nombre à l'aide de la fonction ISNUMERIC.

votre code devrait ressembler à cela:

declare @ProjectID uniqueidentifier
set @ProjectID = null
IF ISNUMERIC(@Project) > 0
BEGIN
set @ProjectID = cast(@Project as uniqueidentifier)
END
if(@ProjectID is null)
BEGIN
select  @ProjectID = ProjectID from Project where projectcode = @Project
END
return @ProjectID

OriginalL'auteur Fernando Meneses Gomes

Vous devez vous connecter pour publier un commentaire.