Les « Tables Valued Parameters »

Published Jan 15 2019 12:29 PM 48 Views
First published on MSDN on Nov 19, 2009

Avant la version SQL Server 2008 il n’était pas possible de passer une table comme paramètre d’une procédure stockée. Si nous prenons l’exemple d’une base de données musicale simplifiée, elle est constituée de trois tables, une pour les artistes, une pour les albums et une pour les chansons. Ajouter un album à la base revient à ajouter des données à chacune de ces tables. On pouvait utiliser différentes procédure stockées pour chaque table, faire des boucles dans l’application ce qui impliquait des allers-retours avec le client.


SQL Server 2008 a introduit les « Tables Valued Parameters » (paramètres table) qui nous permettent de passer un nombre inconnu de données à une procédure stockée.



Pour l’exemple voici les tables nécessaires:



Create Table Artistes (ArtisteID int identity, Artiste  varchar(120))


Create Table Albums (AlbumID int identity, ArtistID int,  Album varchar(120))


Create Table Chansons (ChansonsID int identity, Titre varchar(120), Track int, ArtistID int,  Album varchar(120))



Pour commencer il faut définir un Type de table :



Create Type Tbl_ChansonType as Table


(Titre varchar(120) not null, Track int)


Go



Voici la procédure stockée qui va nous permettre de tout réaliser en une passe :



create procedure AddChanson(


@Artiste varchar(120), @Album varchar(120), @Chanson Tbl_ChansonType  READONLY)


as


begin


set nocount on


-- Ajouter Artiste


Declare @ArtisteID int


insert into Artistes values (@Artiste)


select @ArtisteID = SCOPE_IDENTITY()



-- Ajouter Album


Declare @AlbumID int


insert into Albums values (@ArtisteID, @Album )


select @AlbumID = SCOPE_IDENTITY()



-- Ajouter Chanson


insert into Chansons


select Titre, Track, @ArtisteID, @AlbumID


from @Chanson


end



Voici un exemple d’utilisation depuis T-SQL :



declare @New_Album  as Tbl_ChansonType;


insert  @New_Album values ('Highway Star', 1);


insert  @New_Album values ('Maybe I''m a Leo', 2);


insert  @New_Album values ('Pictures of Home', 3);


insert  @New_Album values ('Never Before', 4);


insert  @New_Album values ('Smoke on the Water', 5);


insert  @New_Album values ('Lazy', 6);


insert  @New_Album values ('Space Truckin', 7);



exec AddChanson 'Deep Purple', 'Machine Head ', @New_Album



Voici un exemple en Visual Basic .Net:



Dim Chansons As New DataTable


Chansons = New DataTable("Tbl_ChansonType")



Dim Row1, Row2 As DataRow



Dim Titre As DataColumn = New DataColumn("Titre")


Titre.DataType = System.Type.GetType("System.String")


Chansons.Columns.Add(Titre)



'definir les colonnes de la table


Dim Track As DataColumn = New DataColumn("Track")


Track.DataType = System.Type.GetType("System.Int32")


Chansons.Columns.Add(Track)



Row1 = Chansons.NewRow()


Row1.Item("Titre") = "Highway Star"


Row1.Item("Track") = "1"


Chansons.Rows.Add(Row1)



Row2 = Chansons.NewRow()


Row2.Item("Titre") = "Smoke on the Water"


Row2.Item("Track") = "5"


Chansons.Rows.Add(Row2)



Dim cmd As New SqlCommand()


cmd.Connection = conn


cmd.CommandType = CommandType.StoredProcedure


cmd.CommandText = "AddChanson"



' Ajout des parameters de la procedure


cmd.Parameters.AddWithValue("@Artiste", "Deep Purple")


cmd.Parameters.AddWithValue("@Album", "Machine Head")


cmd.Parameters.AddWithValue("@Chanson", Chansons)



conn.Open()



cmd.ExecuteNonQuery()



conn.Close()



Nous pouvons constater avec SQL Profiler que nous avons réduit les échanges entre le client et le serveur à une seule exécution RPC pour tous les enregistrements.



declare @p3 dbo.Tbl_ChansonType


insert into @p3 values(N'Highway Star',1)


insert into @p3 values(N'Smoke on the Water',5)



Dernier point, quand doit-on utiliser les opérations BULK ou les « Valued Parameters » ?


En général l’expérience montre que les Bulk Insert sont plus efficace si le nombre de lignes a inséré est supérieur à 1000. Le tableau ci-après résume les différentes configurations :



Source


Logique d’insertion


Nombre de lignes


Meilleure technologie


Fichier


Insertion directe


< 1000


BULK INSERT


Fichier


Insertion directe


> 1000


BULK INSERT


Fichier


Insertion complexe


< 1000


TPV


Fichier


Insertion complexe


> 1000


BULK INSERT


Client


Insertion directe


< 1000


TPV


Client


Insertion directe


> 1000


BULK INSERT


Client


Insertion complexe


< 1000


TPV


Client


Insertion complexe


> 1000


TPV


Fichier = les données proviennent d’un fichier structuré sur le serveur


Client = L’application cliente fournis les données


Complexe = de la logique doit être appliquée aux données avant l’insertion



Fred Pichaut


Version history
Last update:
‎Jan 15 2019 12:29 PM
Updated by: