Sql : sp_prepare, sp_execute, sp_prepexec, sp_unprepare
Aujourd'hui, petite présentation de quelques fonctionnalités intéressantes SQL : sp_prepare, sp_prepexec, sp_execute et sp_unprepare.
L'idée générale est d'utiliser ces api pour :
- Préparer une commande (avec sp_prepare, voir sp_prepexec)
- Exécuter cette commande avec différents paramètres (avec sp_execute)
- Décharger cette commande (avec sp_unprepare)
Le but de ces quelques procédures stockées systèmes étant d'optimiser un lot de requêtes exécutées sur SQL, dont la commande est identique.
Pour informations c'est ce qui se passe lorsque vous utilisez SSIS, dans certains cas.
La seule difficulté (qui n'en est pas une) consiste à récupérer le handle généré par la commande sp_prepare et l'utiliser avec sp_execute, ainsi qu'avec sp_unprepare
Mais voyons ce que cela donne, en pratique (sur une base de données AdventureWorks) :
Tout d'abord, préparer la commande :
Declare @handle int
exec sp_prepare @handle output,
N'@Name nvarchar(50), @RowGuid uniqueidentifier, @ModifiedDate Datetime',
N'Insert into Production.ProductCategory (Name, rowguid, ModifiedDate)
Values (@Name, @RowGuid, @ModifiedDate)';
La commande est prète, on peut effectuer une insertion en masse :
exec sp_execute @handle, @Name=N'Catégorie153',
@RowGuid='34661A8B-9672-49D1-A932-AE682E9DB44C',@ModifiedDate='2008-01-16 10:35:00:117'
exec sp_execute @handle, @Name=N'Catégorie154',
@RowGuid='54661A8B-9672-49D1-A932-AE682E9DB44C',@ModifiedDate='2008-01-16 10:35:00:117'
exec sp_execute @handle, @Name=N'Catégorie155',
@RowGuid='A8661A8B-9672-49D1-A932-AE682E9DB44C',@ModifiedDate='2008-01-16 10:35:00:117'
exec sp_execute @handle, @Name=N'Catégorie156',
@RowGuid='B8661A8B-9672-49D1-A932-AE682E9DB44C',@ModifiedDate='2008-01-16 10:35:00:117'
exec sp_execute @handle, @Name=N'Catégorie157',
@RowGuid='C8661A8B-9672-49D1-A932-AE682E9DB44C',@ModifiedDate='2008-01-16 10:35:00:117'
exec sp_execute @handle, @Name=N'Catégorie158',
@RowGuid='D8661A8B-9672-49D1-A932-AE682E9DB44C',@ModifiedDate='2008-01-16 10:35:00:117'
exec sp_execute @handle, @Name=N'Catégorie159',
@RowGuid='E8661A8B-9672-49D1-A932-AE682E9DB44C',@ModifiedDate='2008-01-16 10:35:00:117'
exec sp_execute @handle, @Name=N'Catégorie160',
@RowGuid='FF661A8B-9672-49D1-A932-AE682E9DB44C',@ModifiedDate='2008-01-16 10:35:00:117'
Et libérer le handle (sp_unprepare)
exec sp_unprepare @handle;
Note :
On peut aussi utiliser la commande sp_prepexec qui va cumuler dans la première instruction sp_prepare et sp_execute. On peut donc aussi écrire :
declare @handle int;
exec sp_prepexec @p1 output,N'@Name nvarchar(50),@RowGuid uniqueidentifier,
@ModifiedDate datetime',
N'Insert into Production.ProductCategory (Name, rowguid, ModifiedDate)
Values (@Name, @RowGuid, @ModifiedDate)',
@Name=N'Catégorie1',@RowGuid='D8F1139C-DC4E-4E3E-91DB-C2945C9DBB65',
@ModifiedDate='2008-01-16 10:35:00:113'
Vous poursuivez ensuite en utilisant sp_execute normalement.
Attention, l'utilisation de sp_prepare ne prend tout son intéret que si vous effectuez une série de requête identique assez conséquente. Inutile de passer par là pour ne faire qu'une ou deux insertions !
Et les procédures stockées !!!
Bien, au vu de ce que nous venons de voir, je vois poindre au loin, une remarque que vous allez certainement vous faire ... "Mais ... pourquoi ne pas utiliser les procédures stockées !!!???"
Alors oui, effectivement, les procédures stockées, c'est mieux, plus élégant, et suremment beaucoup plus répandu et simple. Il n'empèche que vous pouvez trés bien être amenés à faire une insertion en masse en base de données, sans avoir pour autant une proc. stock. à disposition (oui ça arrive aussi !) :)
Intégration dans .NET avec tout ça ?
Et bien vous allez voir que c'est "on ne peut plus simple". En effet, vous possédez sur l'objet SqlCommand une méthode qui s'appelle SqlCommand.Prepare() qui va avoir le bon gout de préparer votre commande, en utilisant sp_prepexec.
Voici un morceau de code qui reprend ce que nous venons de faire, mais en .NET :
using (SqlConnection myConn =
new SqlConnection(
@"Data Source=.\sql2005;Integrated Security=true;Initial Catalog=AdventureWorks"))
{
SqlCommand myComm =
new SqlCommand("Insert into Production.ProductCategory (Name, rowguid, ModifiedDate) " +
"Values (@Name, @RowGuid, @ModifiedDate)", myConn);
myComm.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 50);
myComm.Parameters.Add("@RowGuid", System.Data.SqlDbType.UniqueIdentifier);
myComm.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime);
myComm.Parameters["@Name"].Value = "Catégorie1" ;
myComm.Parameters["@RowGuid"].Value = Guid.NewGuid();
myComm.Parameters["@ModifiedDate"].Value = DateTime.Now;
myConn.Open();
myComm.Prepare();
myComm.ExecuteNonQuery();
for (int i = 2; i <= 10; i++)
{
myComm.Parameters["@Name"].Value = "Catégorie" + i.ToString();
myComm.Parameters["@RowGuid"].Value = Guid.NewGuid();
myComm.Parameters["@ModifiedDate"].Value = DateTime.Now;
myComm.ExecuteNonQuery();
}
myConn.Close();
}
On ne peut plus simple !
Happy coding !