Gérer les fonctions SQL Server avec des scripts

Comment gérer les fonctions SQL Server de type utilisateur avec du code T-SQL ?

Lorsque l’on commence le développement T-SQL, il faut apprendre à gérer les fonctions SQL Server et les différentes syntaxes pour éviter les erreurs, sinon le SQL devient frustrant. Ce tutoriel présente des exemples de code pour créer, modifier, appeler et supprimer les fonctions SQL Server.

Ces exemples de fonctions utilisateur comme Create Function présentent la syntaxe des principales opérations basiques. Tout d’abord, il existe deux grands types de fonctions SQL Server qui sont :

• Les fonctions intégrées ou natives.
• Les fonctions définies par les utilisateurs. Cet article traite des fonctions créées par l’utilisateur uniquement.

1. Gérer les fonctions SQL Server de type utilisateur

Une fonction SQL Server définie par l’utilisateur est un programme Transact-SQL qui accepte des paramètres en entrée, exécutes des actions, comme lire une table SQL et qui retourne un résultat en sortie.

Pour commencer, créer au préalable la table client avec le code disponible sur cette page: Code de création de la table d’exemple CLIENTS.

1.1 Créer une fonction SQL Server avec Create Function

Premièrement, voici la création en T-SQL d’une fonction MS SQL utilisateur qui prend comme paramètre en entrée le numéro de client et retourne le nom du client stocké dans la variable @NomClient.

Le script de création SQL Server create function doit obligatoirement retourner une valeur.

CREATE FUNCTION dbo.ufnListeClients(@NoClient INTEGER)
RETURNS NVARCHAR(20)
AS 
BEGIN 
  DECLARE	@NomClient	AS NVARCHAR(20);
  SELECT	@NomClient = [NOM]
  FROM		dbo.CLIENTS
  WHERE		[NOCLIENT] = @NoClient;
  RETURN	@NomClient;
END
GO

1.2 Modifier une fonction SQL Server avec Alter Function

Deuxièmement, la commande ALTER permet de modifier la fonction créer plus haut pour que cette fois elle ne retourne pas uniquement le nom du client, mais également la ville du client, séparé par un trait. Le résultat de la fonction est stocké dans la variable @NomVilleClient.

ALTER FUNCTION dbo.ufnListeClients(@NoClient INTEGER)
RETURNS NVARCHAR(40)
AS 
BEGIN 
  DECLARE	@NomVilleClient	AS NVARCHAR(20);
  SELECT	@NomVilleClient = [NOM] + '-' + [VILLE] 
  FROM		dbo.CLIENTS
  WHERE		[NOCLIENT] = @NoClient;
  RETURN	@NomVilleClient;
END
GO

Voici un tutoriel Microsoft pour modifier une procédure stockée existante dans une base de données MS SQL.

Modifier une procédure stockée SQL Server (Alter Procedure)

1.3 Supprimer une fonction SQL avec Drop Function

Cette commande SQL permet de supprimer une fonction, sans toutefois faire de test d’existence.

DROP FUNCTION ufnListeClients;
GO

Si la fonction n’était pas dans la base de données alors cette erreur SQL Server s’affiche dans SSMS:

Impossible de supprimer la fonction ‘ufnListeClients’, car elle n’existe pas ou vous n’avez pas la permission.

Le même message d’erreur en Anglais :

Cannot drop the function ‘ufnListeClients’, because it does not exist or you do not have permission.

1.4 Tester l’existence d’une fonction SQL Server avant de la supprimer

Pour éviter cette erreur, il suffit d’ajouter une commande pour tester l’existence de la fonction au préalable.

IF OBJECT_ID (N'dbo.ufnListeClients', N'FN') IS NOT NULL  
    DROP FUNCTION ufnListeClients;  
GO

Utiliser l’option DROP FUNCTION IF EXISTS disponible depuis SQL Server 2016

Depuis SQL Server 2016, il est plus facile de supprimer un objet de la base de données. Il suffit d’utiliser ce code d’une seule ligne qui ne supprime la fonction uniquement si elle dans la base.

DROP FUNCTION IF EXISTS ufnListeClients;

1.5 Comment appeler une fonction utilisateur SQL Server ?

Pour aller plus loin, pour appeler une fonction, utiliser un SELECT suivi du nom de la fonction sans oublier le paramètre, ici c’est le numéro de client. Certaines fonctions gèrent le paramètre par défaut, et dans ce cas-là il n’est pas obligatoire. Par exemple, la première commande retourne le nom du client correspondant au numéro de client 1 et ne nomme pas la colonne.

SELECT dbo.ufnListeClients(1);

Enfin, cet appel de la fonction retourne le nom du client dans une colonne nommée CLIENTS.

SELECT dbo.ufnListeClients(1) AS CLIENTS;

Cet article explique comment gérer les fonctions SQL Server utilisateur via un exemples SQL Server de CREATE FUNCTION. Enfin, pour avoir plus d’informations sur les fonctions SQL Server, vous pouvez lire directement la documentation sur le site de Microsoft. Par ailleurs, la documentation en version Anglaise est plus riche et plus complète que la version Française.

2. Les fonctions scalaires et fonctions tables

Les sous-types de fonctions SQL Server sont les suivants :

• Les fonctions scalaires pour retourner une valeur unique via l’instruction RETURNS.
• Les fonctions table pour renvoyer le type de données table, qui est générée par une requête SELECT.

Les fonctions UDF pour User Defined Functions en Anglais ne peuvent pas être exécutées en parallèle.

3. Les fonctions systèmes

Enfin, les fonctions système sont des fonctions intégrées au SGBD Microsoft et qui ne sont pas modifiables. Quelques exemples comme : GETDATE(), CAST, CONVERT(), ROUND(), etc. Les fonctions systèmes sont de plusieurs types et couvrent un large éventail d’options. Par exemple les fonctions de conversion de données, les fonctions de calcul et d’affichage de la date et de l’heure, ou encore les fonctions de chaines de caractères.

Ci-dessous, un article plus complet pour modifier et appeler une fonction SQL Server. L’exemple permet de modifier une fonction qui liste les clients d’une table. Puis d’afficher le nom du client concaténé avec la ville du client dans la même colonne.

4. Les fonctions SQL Server déterministes et non déterministes

Il existe une propriété des fonctions SQL Server moins connues, c’est le déterminisme. Une fonction déterministe renvoie la même valeur à tout moment, avec les mêmes données dans la base bien sûr. Cependant, une fonction non déterministe ne retourne jamais la même valeur, car elle est liée au temps, comme la fonction de temps CURRENT_TIMESTAMP.

5. Les fonctions CLR

Un autre type de fonctions est disponible, ce sont les fonctions CLR, pour Common Language Runtime en Anglais. Les fonctions CLR nécessite d’intégrer dans l’environnement de développement et dans SQL Server le Framework .NET. Les fonctions CLR peuvent intégrer du code .NET et permettent une application plus large que les fonctions SQL utilisateur classiques.

Voici pour finir un tutoriel complet sur l’ensemble des syntaxes CREATE FUNCTION avec toutes les combinaisons possibles sur le site officiel de Microsoft.

Modifier une fonction SQL Server avec alter function