Les « Tables Valued Parameters »

[Guillaume Fourrat]

First published on MSDN on Nov 19, 2009

Technorati Tags: Katmai , SQL Server , sql2k8 , sql2008 , MSSQL

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