Du code SQL à la procédure stockée Transact-SQL

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
go
DATE                 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     
...

Construction des procédures stockées

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
go

La 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_test
DATE                 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
go

En 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
go

La 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
go

Les 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
go

Les 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
go

La 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
go

Les 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
go

La 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