Executing a Stored Procedure

Executing a stored procedure is similar to executing a prepared statement, except that the stored procedure exists as a permanently compiled object in the database. A stored procedure can also be used to hide complex SQL statements from the application.

When executing a stored procedure in a Command object, the CommandType property must be specified with the adCmdStoredProc value. With adCmdStoredProc, the corresponding SQL statement for the underlining provider is generated. With MSDASQL, the ODBC escape sequences for procedure calls are generated; the Microsoft® SQL Server™ ODBC driver is optimized to take advantage of these sequences.

There is no need to prepare a statement that only calls a stored procedure. Both stored procedures and prepared statements are methods of precompiling statements. Because a stored procedure is already precompiled, preparing a stored procedure call actually adds overhead. The prepared statement adds a small precompiled execution plan that calls the stored procedure execution plan, rather than executing the stored procedure execution plan directly.

This example shows the execution of the sp_who SQL Server system stored procedure:

Dim cn As New ADODB.Connection

Dim cmd As New ADODB.Command

Dim rs As New ADODB.Recordset

  

cn.Provider = "sqloledb"

cn.Properties("Data Source").Value = "MyServerName"

cn.Properties("Initial Catalog").Value = "pubs"

cn.Properties("Integrated Security").Value = "SSPI"

cn.Open

  

Cmd.ActiveConnection = cn

Cmd.CommandText = "sp_who"

Cmd.CommandType = adCmdStoredProc

  

Set rs = Cmd.Execute

Debug.Print rs(0)

rs.Close

See Also

Calling a Stored Procedure (OLE DB)

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.