Microsoft SQL Server/Procédures stockées

Introduction

Ajout d'un serveur lié, il peut être de plusieurs types dont Oracle Database.

Fournisseurs de connexions.

Les procédures stockées sont des ensembles de requêtes SQL enregistrés dans les bases de données. Dans SSMS, on les trouve dans le menu du même nom à côté de celui des tables.

En effet, d'un point de vue de l'architecture logicielle d'une application, comme les longues suites de requêtes avec des structures de contrôles sont propres à leur SGBD, il est préférable de les grouper avec les données, pour permettre de passer d'un SGBD à l'autre sans redévelopper le module de formulaire d’interaction avec l'utilisateur (ex : un site Web peut ainsi passer de MySQL à MSSQL sans être repris intégralement, car il invoque une procédure stockée de même nom, avec les mêmes entrées et sorties, dans les deux SGBD).

Les procédures stockées servent généralement à manipuler les tables de la base où elles se trouvent, mais peuvent également interagir avec celles d'autres bases (dont les noms sont placés en préfixe) du même serveur, ou de serveurs liés. Pour créer un serveur lié dans SSMS, se rendre dans le menu "Objets serveur", puis "Serveurs liés", et remplir le compte à utiliser pour s'y connecter (ou utiliser sp_addlinkedserver^[1] où "sp" signifie "stored procedure").

Exemple de jointure entre deux serveurs :

select *
from table1 t1
inner join [serveur2].[base2].[dbo].[table2] t2 on t2.id = t1.t2_id

Syntaxe

Le langage T-SQL de Microsoft contient quelques améliorations par rapport à la norme SQL :

Par défaut, les guillemets ont un rôle différent des apostrophes qui servent à créer des chaines de caractères. Pour les utiliser de la même façon (par exemple pour les imbriquer), il faut donc lancer SET QUOTED_IDENTIFIER ON.
Dans SSMS, une requête SQL peut être exécutée de trois façons :

Soit directement dans une fenêtre blanche apparaissant quand on clique sur "Nouvelle requête". On peut en sauvegarder le contenu en .sql, pour pouvoir la rouvrir plus tard.
Soit en stockant la requête dans une variable chaine, avant d'exécuter cette dernière avec sp_executesql^[2]. Ce qui a l'avantage de pouvoir y incorporer des variables (ex : nom d'une base de données), mais l'inconvénient de supprimer la coloration syntaxique, l'autocomplétion (IntelliSense^[3]) et le débogage SSMS. Ex :
```
 DECLARE @Requete1 NVARCHAR(MAX)
 DECLARE @MaTable1 NVARCHAR(MAX)
 SET @MaTable1 = 
 SET @Requete1 =  'SELECT * FROM ' + @MaTable1
 EXECUTE sp_executesql @Requete1
```
Soit en exécutant une procédure stockée dans une base de données (à côté des tables), dans laquelle on a enregistré une requête. Ex :
```
 EXEC [MaBase1].[dbo].[MaProcédure1]
```

Cet appel peut être suivi d'arguments, comme une procédure ou fonction en programmation impérative.

En effet, on en distingue deux sortes de variables dans les procédures stockées :

Si elles le sont avec le mot Declare, elles sont privées.
Sans ce mot, elles représentent les variables externes de la procédures, à préciser lors de son exécution :

@DateDebut varchar(8)       --Variable publique obligatoire comme argument
@DateFin varchar(8) = null  --Variable publique facultative
if @DateFin is null set @DateFin=convert(varchar,@DateDebut+1,112)
Declare @Nom varchar(50) --Variable privée

Pour créer une nouvelle procédure stockée :

CREATE PROCEDURE [dbo].[MaProcédure1]

Pour enregistrer une procédure stockée existante, il faut exécuter :

ALTER PROCEDURE [dbo].[MaProcédure1]

Idéalement cette instruction sera présente au début de la procédure stockée suivie de AS, dont exécution aura donc pour effet de l'enregistrer (et pas d'en exécuter le contenu). Pour obtenir son résultat, il faut effectuer un clic droit dessus, puis choisir "Exécuter la procédure stockée..." : cela génère une autre requête SQL qui s'ouvre dans un nouvel onglet au-dessus du résultat, appelant la procédure stockée avec ses paramètres.

SSMS ne tolère pas qu'on sauvegarde une procédure stockée avec des erreurs de compilation. En cas de besoin il faut donc commenter le code en erreurs, ou passer par un fichier .sql (temporaire).

Les messages d'erreur communiquent un numéro de ligne décalé par rapport à celui numéroté par l'interface. Il faut y soustraire le nombre de lignes présentes avant le dernier GO.

Par la suite, ces procédures stockées peuvent ensuite être appelées dans des programmes dans des langages qui contiennent un pilote SQL Server, tels que PHP ou VB, qui en présenteront les résultats.

PRINT

Cette commande affiche des caractères (variables ou constantes) dans l'onglet Messages, contrairement au SELECT qui remplit l'onglet Résultats.

Exemples :

print 'Hello World ! ' -- Affiche Hello World !

declare @n int
set @n = 5

print 'la valeur est : ' + cast(@n as varchar)

Conditions

IF

if @x=1 begin
  print 'x = 1'
end else if @x=2 begin
  print 'x = 2'
end else begin
  print 'x <> 1 et 2'
end

le begin et le end peuvent être facultatifs.

CASE

set @Saison = case 
 when @Datejour = '20110918' then 'été'
 when @Datejour = '20110922' then 'automne'
 else 'autre saison'
end

Pour ajouter une condition WHERE uniquement si une valeur est présente, il faut que dans le cas contraire, la deuxième condition soit toujours vraie (ex : Champ1 = Champ1) :

declare @Colonne int = null
select Champ1
from Table1
where Champ1 = case when isnull(@Colonne,'')<>'' then @Colonne else Champ1 end

À noter que l'exemple ci-dessus serait plus simple avec where Champ1 = isnull(@Colonne,Champ1).

Boucles

WHILE^[4]

La boucle "while" utilise une condition pour s'arrêter, par exemple un compteur :

DECLARE @i int
WHILE @i <= 10
BEGIN
    UPDATE table1
    SET champ2 = "petit" WHERE champ1 = @i
    SET @i = @i + 1

    IF (@i = 100)  
        BREAK; 
END

CURSOR

Un curseur permet de traiter un jeu d'enregistrements ligne par ligne, chacun étant stocké dans les variables suivant le INTO, et réinitialisé après le NEXT^[5]. Toutefois il est relativement lent et doit être remplacé par d'autres techniques quand c'est possible^[6].

On peut par exemple ajouter des caractères sur certaines :

USE Base1
declare @Nom varchar(20)
DECLARE curseur1 CURSOR FOR SELECT Prenom FROM Table1
OPEN curseur1

/* Premier enregistrement de la sélection */
FETCH NEXT FROM curseur1 into @Nom
print 'Salut ' + @Nom

/* Traitement de tous les autres enregistrements dans une boucle */
while @@FETCH_STATUS = 0
  begin
    FETCH NEXT FROM curseur1 into @Nom
    print 'Salut ' + @Nom
  end

CLOSE curseur1;
DEALLOCATE curseur1;

Exécution de procédures depuis d'autres

Tout comme dans l'environnement de développement intégré Visual Basic, il existe un mode d'exécution pas à pas : en pressant F11 à chaque arrêt il est possible de suivre le lancement du programme, tout en surveillant les valeurs des variables en bas à gauche.

Les points d'arrêt sont également disponibles pour personnaliser les pas.

Toute modification de la procédure stockée pendant ce débogage s'affichera, mais ne sera pas pris en compte par le processus.

Pour exécuter une procédure stockée depuis une autre :

ALTER PROCEDURE [dbo].[MaProcédure1]
DECLARE	@resultat int
EXEC	@resultat = [dbo].[MaProcédure2] @Parametre1;
if @resultat = 0 begin
 ...
end

Exceptions

Apparue avec SQL Server 2005, la gestion d'exceptions se présente ainsi :

-- Début de la transaction
BEGIN TRAN
  BEGIN TRY
   -- Exécution
   INSERT INTO Table1(Nom1) VALUES ('ABC')
   INSERT INTO Table1(Nom1) VALUES ('123')
   -- Soumission de la transaction
   COMMIT TRAN
  END TRY
BEGIN CATCH
 -- Annulation de la transaction si erreur
 ROLLBACK TRAN
END CATCH

Recherches

Pour obtenir la liste des procédures stockées contenant une chaine particulière :

SELECT name
FROM sysobjects syso
INNER JOIN syscomments sysc
ON syso.id = sysc.id
WHERE
(syso.xtype = 'P' or
syso.xtype = 'V')
AND
(syso.category = 0)
and text like '%Chaine à rechercher%'
group by name

Références

[1] ttps://msdn.microsoft.com/fr-fr/library/ms190479.aspx

[2] ttps://msdn.microsoft.com/en-us/library/ms188001.aspx?f=255&MSPPError=-2147217396

[3] ttps://msdn.microsoft.com/fr-fr/library/hcw1s69b.aspx?f=255&MSPPError=-2147217396

[4] ttp://msdn.microsoft.com/fr-fr/library/ms178642.aspx

[5] ttp://msdn.microsoft.com/fr-fr/library/ms180169.aspx

[6] ttp://sqlpro.developpez.com/cours/sqlserver/MSSQLServer-avoidCursor/

[1]

[2]

[3]

[4]

[5]

[6]