Une fonction scalaire est identique à une fonction mathématique : elle peut avoir plusieurs
paramètres d’entrée (jusqu’à 1024), et renvoie une seule valeur.
CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
-- Declare the return variable here
DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>
-- Add the T-SQL statements to compute the return value here
SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
-- Return the result of the function
RETURN <@ResultVar, sysname, @Result>
158
END
GO
Ici, nous créons une fonction scalaire que l’on reconnaît grâce à l’instruction RETURNS int.
On se sert bien entendu de l’instruction CREATE FUNCTION suivie du nom de la fonction à
utiliser et des paramètres à prendre en compte entre parenthèses. Les clauses AS BEGIN et
END déclarent dans l’ordre, le début et la fin de la fonction. Cette fonction retourne une
valeur qui désigne le nombre d’articles présents, pour un stock passé en paramètre.
Dans les fonctions, certaines options sont disponibles, comme dans les procédures stockées.
Elles vont vous permettre certaines actions sur cette fonction, et celle-ci est appelable
directement après la définition du type de retour de la fonction :
- WITH SCHEMABINDING : Cette option permet de lier la fonction à tous les objets de la
base auxquels elle fait référence. Dans ce cas, la suppression et la mise à jour de n’importe
quel objet de la base, lié à la fonction est impossible.
- WITH ENCRYPTION : Permet de crypter le code dans la table système.
Exemple 1 : Créons la fonction fn_DateFormat à partir de notre base de données « Papyrus ».
La fonction fn_DateFormat formate à l’aide d’un séparateur entré en paramètre, une date et
la retourne sous forme de chaîne de caractères.
CREATE FUNCTION vente.fn_DateFormat
(@pdate datetime, @psep char (1))
RETURNS char (10)
AS
BEGIN
RETURN
CONVERT (varchar(2), datepart (dd,@pdate))
+ @psep + CONVERT (varchar (2), datepart (mm, @pdate))
+ @psep + CONVERT (varchar (4), datepart (yyyy, @pdate))
END
Nous pouvons maintenant effectuer une requête pour connaître quelles sont les commandes
passées du mois de mars et d’avril.
-- Quelles sont les commandes passées au mois de mars et d'avril ?
SELECT NUMCOM,vente.fn_DateFormat(DATCOM,'/') AS 'Date'
FROM vente.ENTCOM
WHERE MONTH(DATCOM) IN (3,4)
Exemple 2 : Créons la function fn_NbCommandes qui renvoie le nombre de commandes pour
un fournisseur donné avec une réponse formatée :
- Si le nombre de commandes est égal à 0, vous renverrez le message ‘Aucune commande
pour le founisseur xxxx’.
- Si le nombre de commandes est supérieur à 0, vous renverrez le message ‘nnn commandes
pour le fournisseur xxxx’.
CREATE FUNCTION vente.fn_NbCommandes
(@numfou int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @nb int
DECLARE @com varchar(50)
SET @nb = (SELECT count(*) FROM vente.entcom
159
WHERE numfou = @numfou)
-- Si le nombre de commandes est égal à 0, vous renverrez le message
'Aucune commande pour le fournisseur xxxx'
IF @nb = 0
SET @com = 'Aucune commande pour le fournisseur ' + CAST(@numfou AS
varchar)
-- Si le nombre de commandes est supérieur à 0, vous renverrez le message
'nnn commandes pour le fournisseur xxxx'
ELSE
SET @com = CAST(@nb AS varchar) + ' commandes pour le fournisseur ' +
CAST(@numfou AS varchar)
RETURN @com
END
Nous pouvons maintenant tester notre fonction
SELECT DISTINCT vente.fn_NbCommandes(120) AS 'Nombre De Commandes'
FROM vente.entcom
Exemple 3 : Créer la fonction scalaire fn_Date, qui avec l’indice de satisfaction en entrée,
affiche un niveau de satisfaction en clair :
- Indice = Null, 'sans commentaire'
- Indice = 1 ou 2, 'Mauvais'
- Indice = 3 ou 4, 'Passable'
- Indice = 5 ou 6, 'Moyen'
- Indice = 7 ou 8, 'Bon'
- Indice = 9 ou 10, 'Excellent'
CREATE FUNCTION vente.fn_Date
(@indice int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @com varchar(50)
SET @com = CASE
WHEN @indice IS NULL THEN 'Sans commentaire'
WHEN @indice > 0 and @indice < 3 THEN 'Mauvais'
WHEN @indice > 2 and @indice < 5 THEN 'Passable'
WHEN @indice > 4 and @indice < 7 THEN 'Moyen'
WHEN @indice > 6 and @indice < 9 THEN 'Bon'
WHEN @indice > 8 and @indice < 11 THEN 'Excellent'
ELSE 'Hors limite'
END
RETURN @com
END
Testons notre fonction vente.fn_Date :
SELECT NOMFOU, vente.fn_Date(satisf) AS 'Satisfaction'
FROM vente.FOURNISSEUR