Les fonctions définies par l’utilisateur

Les UDF qui renvoient une valeur scalaire sont probablement le type le plus demandé.
A l’instar de GETDATE(), USER() et de plusieurs autres fonctions intégrées propres
à  SQL Server, ce type d’UDF permet de définir du code renvoyant une valeur scalaire
au script, à  la procédure ou à  la ligne de code appelant.

à€ bien des égards, les UDF ressemblent à  des procédures cataloguées. Comme ces
dernières, les UDF admettent des paramètres et renvoient une valeur. Cependant,
contrairement aux procédures cataloguées, les UDF ne peuvent pas disposer de paramètres
en sortie. Plus important, la valeur de retour d’une UDF n’est pas limitée à  un
nombre entier. Au contraire, cette valeur peut être constituée de tout type de
données SQL Server valide, à  l’exception des BLOB, curseurs et horodateurs.

Voyons un exemple qui présente comment construire simplement une fonction permettant
de gérer un problème auquel les développeurs SQL Server sont souvent confrontés,
à  savoir vérifier si la saisie d’un champ datetime s’est produite à  une date spécifique.
Un champ de type datetime possède habituellement des informations spécifiques
de type date et heure qui rendent les comparaisons uniquement sur les saisies
de dates difficiles. Plus spécifiquement, deux champs possédant la même date mais
des heures différentes ne seront pas considérés comme égaux.

Une solution classique consiste à  convertir la date en une chaîne de caractères,
puis de nouveau en date après avoir tronqué les données relatives à  l’heure. Si
on réalise cette conversion sur les deux champs de type datetime que l’on souhaite
comparer, la comparaison sur la seule date (à  l’exclusion de l’heure) fonctionnera.
La solution peut ressembler au code présenté dans le listing 1, dont voici le
résultat :

(1 row(s) affected)

KeyCol TestDate
——- ———–
(0 row(s) affected)

KeyCol TestDate
——- ———–
12000-02-10 21:29:43.140

(1 row(s) affected)

La deuxième requête est un peu plus complexe; plus on imbrique de fonctions dans
une requête, moins le code est lisible. Il m’est quelquefois arrivé de reformater
plusieurs dates pour en extraire uniquement le jour, sans l’heure. Cependant,
le code résultant était difficile à  lire.
Maintenant, je fais les mêmes opérations avec une UDF simple. D’abord, j’utilise
la nouvelle commande CREATE FUNCTION pour créer la fonction. Je formate cette
commande de manière très analogue à  une procédure cataloguée. Je pourrais écrire
la requête de la manière suivante :

CREATE FUNCTION DayOnly(@Date datetime)
RETURNS varchar(12)
AS
BEGIN
RETURN CONVERT(varchar(12), @Date, 101)
END

Désormais, je reformate la requête légèrement pour tirer profit de la nouvelle
fonction :

SELECT *
FROM TestTable
WHERE dbo.DayOnly(TestDate) = dbo.DayOnly(GETDATE())

Même pour cette requête simple, le nouveau code est beaucoup plus lisible. L’appel
de la fonction est structuré de la même manière que dans la plupart des langages
de programmation qui supportent les fonctions, avec toutefois une particularité
: le propriétaire (schéma) est nécessaire pour exécuter la fonction. Pour quelque
obscure raison, SQL Server ne résout pas les fonctions comme il résout les autres
objets.
Les UDF présentent d’autres avantages que la simple lisibilité. On peut imbriquer
des requêtes dans des UDF et ainsi les utiliser pour encapsuler des sous-requêtes.
En fait, il est possible d’encapsuler pratiquement toute requête renvoyant une
valeur discrète. Pour illustrer ce point, encapsulons l’exemple de sous-requête
de stock provenant de SQL Server Books Online (BOL). La version utilisant la sous-requête
ressemble au listing 2. Les versions encapsulées des sous-requêtes calculant la
moyenne et la différence de prix sont proposées dans les fonctions du listing
3. Notez qu’il est permis d’imbriquer une UDF dans une autre.
Le listing 4 contient le résultat de la requête BOL que j’ai modifiée pour utiliser
les nouvelles fonctions au lieu du vieux modèle de sous-requête. L’exécution de
cette requête génère les mêmes résultats que l’exécution de la sous-requête. En
plus d’être plus lisible, l’UDF est réutilisable, ce qui permet de gagner du temps
au fur et à  mesure que les fonctions deviennent plus complexes.