Introduction
Ce document détaille les étapes permettant de passer d’un code SQL simple à une procédure stockée Transact-SQL.
Cela peut paraître étrange mais il y a encore beaucoup de réticences à exploiter la fonctionnalité des procédures stockées T-SQL avec Sybase et MS SQL Server pour retourner des jeux de résultats. Les raisons invoquées sont multiples : normes ANSI, indépendence de l’application par rapport au langage procédural d’un moteur SGBD, etc. Cependant les procédures stockées permettent de délocaliser les traitements en bénéficiant de toute la puissance du moteur SGBD et facilitent aussi les besoins en dénormalisations et calculs.
Convention : le passage de dates est parfois problématique dans le cadre de
la gestion multi-langues. Le format 'AAAAMMJJ' est universel et sera toujours
correctement interprété, aussi, dans ce document, les dates sont toujours
traitées avec le format 'AAAAMMJJ'.
Spécifications
La requête SQL à transformer en procédure stockée Transact SQL est la suivante :
select DATE, NB=count(1) from QUOTE where INSTRUMENT=351197 and DATE between '20090101' and '20090201' group by DATE goDATE NB -------------------- --------- Jan 1 2009 12:00AM 17 Jan 2 2009 12:00AM 45 Jan 3 2009 12:00AM 13 Jan 4 2009 12:00AM 7 ...
CREATE PROCEDURE
create procedure dbo.sp_test
as
begin
select DATE,
NB=count(1)
from QUOTE
where INSTRUMENT=351197
and DATE between '20090101' and '20090201'
group by DATE
end
goLa syntaxe de création de la procédure est ici proche de celle d’une vue. Le
code SQL initial est simplement encapsulé dans une structure create proc ... as
begin ... end. Son exécution est des plus triviales avec la commande exec ou
execute :
execute dbo.sp_testDATE NB -------------------- --------- Jan 1 2009 12:00AM 17 Jan 2 2009 12:00AM 45 Jan 3 2009 12:00AM 13 Jan 4 2009 12:00AM 7 ...
Le propriétaire du schéma de la base est précisé à la création comme à l’exécution de la procédure stockée. Les procédures stockées apportent une sécurité supplémentaire : les droits d’exécution d’une procédure sont donnés à un utilisateur sans pour autant permettre à cet utilisateur d’accéder directement aux objets appelés dans la procédure.
Cet exemple paraît inutile mais il procure immédiatement un gain de performance non négligeable à l’exécution. En effet le plan d’exécution associé à la requête est calculé et stocké lors du premier appel. Toutes les exécutions suivantes n’auront pas à réaliser de nouveau cette compilation, contrairement à du code sql dynamique.
Les échanges réseau sont également améliorés (à ponderer avec la taille des paquets).
Passage de paramètres
Dans sa forme actuelle, la procédure est certes pratique et efficace mais figée. On devrait pouvoir faire évoluer la période mais aussi l’instrument voulu. La procédure peut accepter et exploiter des paramètres. Exemple :
drop procedure dbo.sp_test
go
create procedure dbo.sp_test
@instrument int,
@from_date datetime ,
@to_date datetime
as
begin
select DATE,
NB=count(1)
from QUOTE
where INSTRUMENT=@instrument
and DATE between @from_date and @to_date
group by DATE
end
goEn Transact-SQL, une variable est identifiée par le caractère @ et associé à
un type. Ici, la position des trois variables @instrument, @from_date et
@to_date entre le nom de la procédure stockée et le mot-clé 'as' indique qu’il
s’agit de paramètres.
Les paramètres sont donnés à la procédure stockée dans leur ordre de déclaration :
execute dbo.sp_test 351197,'20090101', '20090201'Ils peuvent tout aussi bien être passés de manière déclarative, dans ce cas l’ordre n’importe pas :
execute dbo.sp_test @instrument=351197,@from_date='20090101', @to_date='20090201'Valeurs par défaut
Il peut être nécessaire de vouloir voir un comportement automatique dans la procédure stockée, comme par exemple appliquer la date courante si la date de fin n’est pas définie, ou encore, si la date de début de période n’est pas fournie, forcer celle-ci au premier jour du mois.
La première étape consiste à ajouter à la déclaration du type des variables
une valeur par défaut. Le résultat d’une fonction peut être donné en guise de
valeur par défaut comme par exemple getdate().
Le but du jeu est ensuite d’interpréter ces valeurs et d’adopter un comportement en conséquence :
create procedure dbo.sp_test
@instrument int,
@from_date datetime = null,
@to_date datetime = getdate()
as
begin
select @from_date=isnull(@from_date,
dateadd(day,
1-datepart(day,@to_date),
@to_date
)
)
select DATE,
NB=count(1)
from QUOTE
where INSTRUMENT=@instrument
and DATE between @from_date and @to_date
group by DATE
end
goLa séquence de détermination de la valeur @from_date signifie : si la
variable @from_date est 'null' alors elle est définie à partir du contenu de
@to_date, en soustrayant à la date en cours le numéro de jour du mois.
@from_date est alors définie au premier jour du mois.
Une autre solution pouvait consister à effectuer une double conversion date
=> chaîne => date, mais cette solution est nettement moins élégante :
select convert(datetime, convert(varchar(6),@to_date,112) + '01' ).
Avec la mise en place de valeurs automatiquement définies ou calculées dans la procédure pour les variables, certains paramètres peuvent être dès lors omis :
execute dbo.sp_test 351197
execute dbo.sp_test 351197, '20090801'
execute dbo.sp_test 351197, null, '20090720'Le langage procédural Transact-SQL
Le code imbriqué dans une telle stucture bénéficie des méthodes usuelles de programmation procédurale :
- conditions (
if begin end else begin end). - boucles (
while begin end).
La gestion de la valeur @from_date peut être gérée procéduralement de la
façon suivante pour traiter le cas où @from_date n’a pas de valeur :
create procedure dbo.sp_test
@instrument int,
@from_date datetime = null,
@to_date datetime = getdate()
as
begin
if @from_date is null
begin
set @from_date=dateadd( day,
1-datepart(day,@to_date),
@to_date
)
end
select DATE,
NB=count(1)
from QUOTE
where INSTRUMENT=@instrument
and DATE between @from_date and @to_date
group by DATE
end
goLes variables
Au sein d’une procédure, il est possible de déclarer et d’utiliser des variables. Elles sont locales, c’est à dire existantes uniquement dans le contexte d’exécution de l’utilisateur.
Elles sont déclarées avec le mot clé declare, et comme les paramètres sont
identifiées par @ et typées.
L’affectaction d’un contenu à une variable est réalisée par l’opération
select @var=valeur, ou set @var=valeur.
create procedure dbo.sp_test
@instrument int,
@from_date datetime = null,
@to_date datetime = getdate()
as
begin
declare @jour_du_mois smallint
if @from_date is null
begin
set @jour_du_mois=datepart(day,@to_date)
set @from_date=dateadd( day,1-@jour_du_mois,@to_date)
end
select DATE,
NB=count(1)
from QUOTE
where INSTRUMENT=@instrument
and DATE between @from_date and @to_date
group by DATE
end
goLes tables temporaires
Une table temporaire est une table ordinaire dont la structure et le contenu ne sont visibles que dans le contexte d’exécution d’un utilisateur.
La table temporaire est déclarée en préfixant son nom par le caractère #,
caractère spécial qui indique ainsi au moteur sa nature temporaire : le moteur
prend en charge le stockage dans tempdb, l’unicité du nom, l’appartenance à la
session, la suppression à la fin de la procédure stockée…
Deux méthodes de création possibles : la syntaxe classique CREATE TABLE ou
l’instruction SELECT INTO qui écrit le résultat d’une commande SELECT dans
(INTO) une table.
Pour ajouter une fréquence d’utilisation (pourcentage) de la table QUOTE
pour une période donnée, une table temporaire va être un moyen efficace de
réaliser le calcul. Plus globalement, les tables temporaires sont un atout
majeur pour les dénormalisations intrinsèques et les calculs d’aggrégats.
create procedure dbo.sp_test
@instrument int,
@from_date datetime = null,
@to_date datetime = getdate()
as
begin
declare @jour_du_mois smallint
declare @nb_pct int
if @from_date is null
begin
set @jour_du_mois=datepart(day,@to_date)
set @from_date=dateadd( day,1-@jour_du_mois,@to_date)
end
select DATE,
NB=count(1)
into #temptable
from QUOTE
where INSTRUMENT=@instrument
and DATE between @from_date and @to_date
group by DATE
select @nb_pct=sum(NB) from #temptable
select DATE,
NB,
NB_PCT=convert(int,100*NB/@nb_pct)
from #temptable
drop table #temptable
end
goLa commande finale drop table #temptable n’est pas indispensable, dès la fin
de la procédure la ressource est automatiquement libérée.
L’exécution permet d’obtenir le résultat suivant :
execute dbo.sp_test 351197,'20090101', '20090201'DATE NB NB_PCT -------------------- --------- -------- Jan 1 2009 12:00AM 17 5 Jan 2 2009 12:00AM 45 8 Jan 3 2009 12:00AM 13 4 Jan 4 2009 12:00AM 7 2 ...
La gestion d’erreurs
En Transact-SQL, une erreur ne provoque pas systématiquement l’arrêt du traitement. Autrement dit, l’instruction suivant la commande en echec est exécutée.
Le moteur indique l’état de chaque commande par l’intermédiaire de la
variable @@error. Ce comportement est systématique, ce qui signifie que la
lecture même de la variable @@error modifie son contenu. Pour cette raison, il
est indispensable de stocker au préalable la valeur dans une variable
locale.
Toute autre valeur que 0 dans cette variable indique une erreur.
create procedure dbo.sp_test
@instrument int,
@from_date datetime = null,
@to_date datetime = getdate()
as
begin
declare @jour_du_mois smallint
declare @nb_pct int
declare @errno int
if @from_date is null
begin
set @jour_du_mois=datepart(day,@to_date)
set @from_date=dateadd( day,1-@jour_du_mois,@to_date)
end
select DATE,
NB=count(1)
into #temptable
from QUOTE
where INSTRUMENT=@instrument
and DATE between @from_date and @to_date
group by DATE
set @errno=@@error
if @errno != 0
begin
print 'erreur'
return -1
end
select @nb_pct=sum(NB) from #temptable
select DATE,
NB,
NB_PCT=convert(int,100*NB/@nb_pct)
from #temptable
set @errno=@@error
if @errno != 0
begin
print 'erreur'
return -2
end
return 0
end
goLes commentaires
La procédure est prête, ou presque. Pour des raisons de lisibilité et de maintenance, le code va être surchargé par des commentaires techniques et fonctionnels.
Deux solutions : le double tiret --, déjà utilisé précédemment, ou la paire
/* */ pour commenter un bloc entier sur plusieurs lignes
create procedure dbo.sp_test
/*
Procedure : sp_test
Objet : retourne des lignes
Usage : sp_test INBSTRUMENT, 'AAAAMMJJ' , 'AAAAMMJJ'
Exemple : sp_test 7 , '20090901', '20091001'²
*/
@instrument int,
@from_date datetime = null,
@to_date datetime = getdate()
as
begin
-- --------------------------------------------------------
-- environnement
declare @jour_du_mois smallint
declare @nb_pct int
declare @errno int
-- --------------------------------------------------------
-- dates par defaut
if @from_date is null
begin
set @jour_du_mois=datepart(day,@to_date)
set @from_date=dateadd( day,1-@jour_du_mois,@to_date)
end
-- --------------------------------------------------------
-- Extraction des donnees
select DATE,
NB=count(1)
into #temptable
from QUOTE
where INSTRUMENT=@instrument
and DATE between @from_date and @to_date
group by DATE
set @errno=@@error
if @errno != 0
begin
print 'erreur'
return -1
end
-- --------------------------------------------------------
-- recuperation du total
select @nb_pct=sum(NB) from #temptable
-- --------------------------------------------------------
-- résultat
select DATE,
NB,
NB_PCT=convert(int,100*NB/@nb_pct)
from #temptable
set @errno=@@error
if @errno != 0
begin
print 'erreur'
return -2
end
return 0
end
goLa livraison
Le dernier texte définissant la procédure ne diffère pas tant du code SQL initial. Des paramètres, variables et tables temporaires sont introduits, mais le texte SQL initial, enrichi, demeure similaire à la requête SQL ad-hoc.
Il ne reste plus qu’à attribuer les permissions d’exécution aux utilisateurs souhaités, et c’est terminé.
grant execute on sp_test to monuser