Microsoft SQL Server/Fonctions

Procédures stockées

Bases de données spatiales

Microsoft SQL Server

Annexes

Version imprimable

[ Modifier le sommaire ]

Procédures stockées

Bases de données spatiales

Introduction

On distingue deux types de fonctions :

scalaire : renvoie n'importe quel type de données applicables aux champs, hormis text, ntext, image, cursor et timestamp^[1].
table : renvoie une table.

Environnement SERVERPROPERTY

La fonction suivante permet de récupérer le nom du serveur courant :

SELECT SERVERPROPERTY('MachineName')

Condition ISNULL

Cette fonction ne renvoie pas vrai si la variable est nulle, comme dans d'autres langages de programmation, mais plutôt son second paramètre, un substitut à NULL obligatoire. Ceci permet de lever les exceptions sur les variables nulles directement dans les requêtes, sans ajout de ligne supplémentaire.

Voici un exemple où Les NULL sont traités comme les vides :

select Champ1 = case when isnull(@Colonne,'')='' then '*' else @Colonne end
from Table1

Extrémums : MIN, MAX

Les fonctions Min() et Max() renvoient respectivement le minimum et le maximum d'une liste de champs.

select min(Date) from Calendrier where RDV = 'Important'

Conversions : CAST et CONVERT

CAST modifie le type d'une variable :

 cast(Champ as decimal(12, 6)) -- sinon '9' > '10'

CONVERT modifie le type d'une variable en premier paramètre, et sa longueur en second.

 convert(varchar, Champ1, 112)
 convert(datetime, Champ2, 121)       -- sinon impossible de parcourir le calendrier (ex : J + 1)

Tous les types de variable ne sont pas compatibles entre eux^[2].

Exemple de problème :

 select Date1
 from Table1
 where Date1 between '01/10/2013' and '31/10/2013'

Les dates ne sont pas forcément reconnues sans utiliser convert. La solution est donc de stocker les dates si possible dans le format datetime :

 select Date1
 from Table1
 where Date1 between convert(varchar,'20131001',112) and convert(varchar,'20131031',112)

Si par contre la date du paragraphe ci-dessus est stockée en varchar avec des slashs, il devient obligatoire de la reformater pour pouvoir la comparer.

De nombreux formats de dates sont disponibles^[3].

Arrondis : FLOOR, CEILING, ROUND

Pour arrondir un nombre :

À l'inférieur : floor(nombre).
Au supérieur : ceiling(nombre).
Au nombre de chiffres précisé : round(nombre, chiffres). Ex :
round(499, -3) = 0.

round(500, -3) = 1000.

round(0.45, 1) = 0.50.

round(0.44, 1) = 0.40.

Pour arrondir une opération sur un entier (ex : COUNT), il faut le convertir sinon il s'arrondit à l'inférieur. Ex :

SELECT ceiling(13 / 12) // = 0
SELECT ceiling(convert(decimal(12, 6), 13) / 12) // = 1

Troncatures : LEFT, RIGHT, et SUBSTRING

Permettent de découper des chaines de caractères selon les positions de leurs caractères^[4].

 select substring('13/10/2013 00:09:19', 7, 4) -- renvoie les quatre caractères à partir du septième, soit "2013"

Par exemple dans le cas de la date avec slashs vue dans le paragraphe précédent :

 select Date1
 from Table1
 where right(Date1, 4) + substring(Date1, 4, 2) + left(Date1, 2) between convert(varchar,'20131001',112) and convert(varchar,'20131031',112)

Recherche CHARINDEX

Pour rechercher la position d'un mot dans une chaine de caractères^[5] :

select CHARINDEX ('table2', 'table1 table2 table3');
-- égal 8

Manipulations : REPLACE et STUFF

Permettent de remplacer des caractères dans une chaine selon leur valeur^[6] : rechercher et remplacer.

Par exemple pour mettre à jour le chemin d'un répertoire renommé^[7] :

update Table1
SET Champ1 = replace(Champ1,'\Ancien chemin\','\Nouveau chemin\')
where Champ1 like '%\Ancien chemin\%'

Dates

Format date

La fonction GETDATE est utilisée pour la date courante. Pour obtenir plutôt la date d'un jour donné au format date, il faut utiliser CONVERT :

select convert(smalldatetime, '2016-01-02', 121)

Découpage

La fonction DATEPART extrait une partie de date sans avoir besoin de son emplacement^[8].

Toutefois, trois fonctions permettent d'accélérer l'écriture de ce type d'extractions :

-- Jour
select day(getdate())
-- Mois
select month(getdate())
-- Année
select year(getdate())
-- Année précédente
select getdate(), 'Année précédente : ' + str(year(getdate()) - 1)

Noms

La fonction DATENAME()^[9] permet d'obtenir les noms des éléments d'une date. Ex :

select datename(month, '20160712')   -- affiche "juillet"
select datename(weekday, '20160712') -- affiche "mardi"

Addition et soustraction de jours

Voici deux fonctions de manipulation de dates^[10] :

DATEDIFF calcule l’intervalle entre deux dates^[11].
DATEADD retourne la date issue d'une autre plus un intervalle^[12].

-- Dernier jour du mois précédent
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
-- Dernier jour du mois courant
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
-- Dernier jour du mois prochain
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))

Exemple :

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,'20150101'),0)) as date

donne :

date
2014-12-31 23:59:59.000

Divers

COALESCE

Ce verbe anglais signifie "fusionner". La fonction affiche la première colonne en paramètre, puis si elle est nulle la deuxième, et ainsi de suite.

Exemple :

SELECT Champ1, Champ2, Champ3
FROM Table1
/* Affiche :
1 NULL NULL
NULL 2 NULL
NULL NULL 3
*/

SELECT COALESCE(Champ1, Champ2, Champ3)
FROM Table1
/* Affiche :
1
2
3
*/

RAND

Abréviation de random (aléatoire), génère un nombre au hasard entre zéro et un^[13].

Exemple :

SELECT rand(), rand()
-- 0,733812301862999        0,043991986929891

Personnalisée

On peut aussi créer ses propres fonctions avec CREATE FUNCTION^[14], qui sont stockées dans un menu séparé à côté des tables et des procédures stockées.

CREATE FUNCTION MaFonction1(@Paramètre1 varchar) returns varchar AS
BEGIN
  return 'Hello ' + @Paramètre1
END

Triggers

Les triggers sont des scripts qui se déclenchent selon certains évènements, et sont créés avec CREATE TRIGGER^[15]. Dans SSMS, on les trouvent dans le menu Déclencheurs de base de données.

Par exemple pour afficher un texte à chaque création de base :

CREATE TRIGGER MonTrigger1
ON ALL SERVER 
FOR CREATE_DATABASE 
AS
BEGIN
  PRINT 'Une base a été créée.'
END

Parmi les évènements disponibles, on trouve :

FOR CREATE_DATABASE
IF UPDATE
IF INSERTED

Les actions à entreprendre peuvent être :

INSTEAD OF INSERT : l'action du trigger remplace une insertion.
INSERT AFTER
UPDATE AFTER

EVENTDATA

EVENTDATA est une fonction qui renvoie une valeur XML^[16] si elle est référencée dans un trigger.

Références

[1] ttps://technet.microsoft.com/fr-fr/library/ms177499(v=sql.105).aspx

[2] CONVERT

[3] ttp://stackoverflow.com/questions/74385/how-to-convert-datetime-to-varchar

[4] SUBSTRING

[5] ttps://msdn.microsoft.com/fr-fr/library/ms186323.aspx

[6] STUFF

[7] REPLACE

[8] DATEPART

[9] ttps://msdn.microsoft.com/fr-fr/library/ms174395.aspx

[10] ttp://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/

[11] DATEDIFF

[12] DATEADD

[13] ttps://msdn.microsoft.com/fr-fr/library/ms177610.aspx

[14] ttps://msdn.microsoft.com/fr-fr/library/ms186755(v=sql.120).aspx

[15] ttps://msdn.microsoft.com/fr-fr/library/ms189799(v=sql.100).aspx

[16] ttps://msdn.microsoft.com/fr-fr/library/ms173781.aspx

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

[10]

[11]

[12]

[13]

[14]

[15]

[16]