Procédure stockée avec paramètres
Les procédures stockées possèdent généralement des paramètres. Il existe principalement deux méthodes pour passer des paramètres lors de l'exécution d'une procédure stockée, chacune présentant des avantages et inconvénients.
Utilisation de la méthode Refresh
La méthode la plus simple pour le programmeur est d'utiliser la méthode Refresh sur la collection Parameters. Cette méthode a pour avantage de demander de façon dynamique à la source de données quels sont les paramètres de la procédure stockée (Nom, type, …). L'inconvénient se situe au niveau des performances, car on interroge la source de données et on demande des informations en retour, avant de pouvoir exécuter la procédure. De plus, tous les fournisseurs OLEDB ne supportent pas forcément la méthode Refresh (par exemple, le fournisseur natif pour Oracle, MSADORA, ne supporte pas cette méthode alors que les fournisseurs SQLOLEDB ou bien MSDASQL la supporte).
Cet exemple de code montre comment utiliser la méthode Refresh
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strConnOLEDB As String
strConnOLEDB = "Provider=SQLOLEDB;Data Source=martinet03;Initial Catalog=Pubs;User ID=sa;Password="
Set cn = CreateObject("ADODB.Connection")
cn.Open strConnOLEDB
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "byroyalty"
cmd.Parameters.Refresh
cmd.Parameters(1).Value = 30
Set rs = cmd.Execute
While Not rs.EOF
Debug.Print rs.Fields(0).Value
rs.MoveNext
Wend
cn.Close
Set cmd = Nothing
Set cn = Nothing
Création des objets Parameter
La seconde méthode consiste à créer les objets Parameter nécessaires à la procédure stockée, et à les ajouter à la collection Parameters d'un objet Command. Cette méthode est plus difficile et plus fastidieuse pour le programmeur, car elle nécessite de connaître précisément les paramètres (nom, type, longueur) et d'écrire le code nécessaire à la création des paramètres. Par contre, elle a l'avantage d'être plus rapide en temps d'exécution que l'utilisation de la méthode Refresh. Cette méthode est donc fortement recommandée en raison de ses performances.
Code de la procédure stockée proc_testParams possédant 2 paramètres en entrée et 1 en sortie
CREATE PROCEDURE [proc_testParams]
@lastname varchar(30) ,
@firstname varchar(18) ,
@result int OUTPUT
AS
SELECT @result = count (au_lname)
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id
WHERE au_fname LIKE = firstname
AND au_lname LIKE = lastname
Code Visual Basic: exécution de procédure stockée avec paramètres
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strConnOLEDB As String
strConnOLEDB = "Provider=SQLOLEDB;Data Source=martinet03;Initial Catalog=Pubs;User ID=sa;Password="
Set cn = CreateObject("ADODB.Connection")
cn.Open strConnOLEDB
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "proc_testParams"
With cmd
.Parameters.Append .CreateParameter("lastname", adVarChar, _
adParamInput, 30, "Green")
.Parameters.Append .CreateParameter("firstname", adVarChar, _
adParamInput, 18, "Marjorie")
.Parameters.Append .CreateParameter("result", adInteger, _
adParamOutput)
End With
cmd.Execute
Debug.Print cmd.Parameters(2).Value
cn.Close
Set cmd = Nothing
Set cn = Nothing
....
Partager