Executing Queries

Overview

Once a connection has been opened, a query or stored procedure can be executed in many ways. 

• Execute (ADO Connection): This can be used to execute any SQL statement (including stored procedures). The returned record set is a read only, forward-only cursor.
  
      dim objRec
      set objRec = objConn.Execute( "select * from table1", ,adCmdText)
  
• Execute (ADO Command): This can be used to execute any SQL statement (including stored procedures). The main advantage of the command object is the ability to pass parameters to and from a stored procedure. The Command object is used to issue commands to the database. The returned record set is a read only, forward-only cursor
  • The Command object either connects to a new connection or open a new connection depending on the setting of its ActiveConnection property.
  • More than one Command object can use the same connection.
  • A command can generate one recordset, many recordsets, or none at all.
  • A command can be prepared when repeatedly executing a parameterized query or a  stored proc (Prepare creates the execution plan at first execution, allowing the execution plan to be used without regeneration the next time the query is run. Only use if running the query many times.)
  • If it is known in advance that no records are returned include the adExecuteNoRecords in the CommandType property to optimize performance.
• Open (ADO Recordset): Use this method when you need updateable recordsets.

Detailed

• The Parameters collection belongs to the Command object. It is a collection of Parameter objects used to specify parameters for parameterized queries / stored procedures. Therefore, the Parameters collection is only needed when the SQL command requires parameters.
  • A parameter in a parameterized query or stored proc can be input, output, input/output or a return value.
  • Each parameter in a parameterized query or stored proc must be represented as a Parameter object.
  • Information about individual parameters are then written or read through the corresponding Parameter object.

  Executing commands with the Command object consists of 

  • Setting CommandType, CommandText properties,
  • Creating and appending parameters for parameterized queries,
  • Executing the command,
  • Assigning the resulting set to an ADO result set.

Executing a simple SQL statement (no parameters)

‘Assume a connection has already been opened

‘ Set the command object properties (active connection, command type, etc.)
objCmd.ActiveConnection = objConn
objCmd.CommandType      = adCmdText
objCmd.CommandText      = "select * from SomeTable"

‘ Execute the command object (returns read-only, forward-only cursor)
set objRS = objCmd.Execute

Executing a parameterized query

Parameters in a parameterized query are indicated by a question mark character, ‘?’. ADO will automatically create and append a parameter to the command object for each ‘?’ it finds in the query.

‘Assume a connection has already been opened
objCmd.ActiveConnection = objConn
objCmd.CommandType      = adCmdTxt
objCmd.CommandText      = "select * from T1 where f1 = ?"

‘ ADO already created a parameter for use. Set its value
With objCmd.Parameters.Item(0)
    .Value     = "White"
    .Direction = adParamInput
    .Type      = adVarChar
    .Size      = 40
End With

‘Execute the command object (returns read-only, forward-only cursor)
set objRs = objCmd.Execute

Executing a parameterized stored procedure

Note that with adCmdStoredProc constant, the query string is executed using the ODBC escape function syntax: {? = call procedure(?)}. Never prepare the execution of a stored procedure. This example assumes that the stored proc takes two input parameters

create procedure StoredProcName 
@Param1 int, 
@Param2 int 
AS
...
return 0

‘Assume a connection has already been opened
objCmd.ActiveConnection = objConn
objCmd.CommandType      = adCmdStoredProc
objCmd.CommandText      = StoredProcName

‘ Create the parameters one by one and add them to the parameters collection
set intParam = objCmd.CreateParamter("ID", adInteger, adParamInput, 4, 10)
objCmd.Parameters.Append intParam ' this param has a value of 10

set strParam = objCmd.CreateParameter("name", adChar, adParamInput, 255, "yazan")
objCmd.Parameters.Append strParam ' this param has a value of "yazan"

‘Execute the command object (returns read-only, forward-only cursor)
set objRs = objCmd.Execute

Executing a Parameterized Stored Proc with return values

• Output and return values are not returned until the data in the recordset has been completely fetched or the recordset had been closed.
• The return value must always be parameter at index zero.
• Instead of creating the parameter objects manually and appending them to the Parameters collection, we do a Parameters.Refresh to set up the parameters automatically, but the expense of a server network trip.
• Note that when doing Parameters.Refresh, any return values in the stored proc will be at index zero.

' Create a command object to execute a stored proc
Dim oCmd As New ADODB.Command
oCmd.ActiveConnection = gCN
oCmd.CommandText      = "CmdWithParamExample"
oCmd.CommandType      = adCmdStoredProc

' Set up parameters for the stored proc: It takes an input, an output and 
' returns a value. All three are integers No need to create a parameter dim
' for each parameter. Use the  same parameter object
Dim oPrm As ADODB.Parameter

    ' Return value
    Set oPrm = oCmd.CreateParameter("Ret", adInteger, adParamReturnValue, 4)
    oCmd.Parameters.Append oPrm

    ' Input
    Set oPrm = oCmd.CreateParameter("In", adInteger, adParamInput, 4, 10)
    oCmd.Parameters.Append oPrm

    ' Output
    Set oPrm = oCmd.CreateParameter("Out", adInteger, adParamOutput, 4, -10)
    oCmd.Parameters.Append oPrm

' Execute the command. Note that since we're using a client-side cursor, ADO 
' will return a static cursor and not dynamic as we have requested!
Dim oRs As New ADODB.Recordset
oRs.CursorLocation = adUseClient
oRs.Open oCmd, , adOpenDynamic

' Since it's static, we can get the count
Dim lRecCount As Integer
lRecCount = oRs.RecordCount

' The output and return values are only available after the recordset has 
' been closed
Debug.Print oCmd.Parameters.Item("In")
Debug.Print oCmd.Parameters.Item("Out")
Debug.Print oCmd.Parameters.Item("Ret")

' Clean up
Set oRs = Nothing
Set oCmd = Nothing

CREATE PROCEDURE CmdWithParamExample
@Param1 int,
@RetrunParam int output

// Perform some SQL operations
...

// Assign a value to the output parameter
set @ReturnParam = 100

// return a value from the stored procedure
return -1