How To Call a Parameterized SQL Server Stored Procedure from ADO

MORE INFORMATION

The sample uses the pubs database in SQL Server to demonstrate the use of the ADO command object to call parameterized queries. The pubs database contains a stored procedure called byroyalty that accepts an integer parameter and returns all authors from the Titleauthor table whose royalty amount matches the passed value. Although this stored procedure does not return any values to the caller, the example reserves space for a return value in the Parameters collection for illustrative purposes.

The code demonstrates three methods of calling this parameterized query.

The first method treats the stored procedure as a command to be executed. It does not require a complicated calling syntax, but cannot return values from the stored procedure.

The second method also treats the stored procedure as a command to be executed. However, it uses the Refresh method of the parameters to automatically populate the parameters collection with information about parameter data types. This is undesirable because there is a substantial performance hit associated with returning parameter information from the server. This method does allow return values from the stored procedure.

The third method requires that you manually populate the Parameters collection with parameter information. From a performance standpoint, this method is less expensive than automatically populating the parameters collection, but it does require the programmer to specify the parameter binding information. It uses the CreateParameter method of the Command object to create a parameter of a specific type, and the Append method of the Parameter object to add the parameter to the Parameters collection.

To use this example, you must have Microsoft Data Access Components (MDAC) version 2.x or later installed, which is included in the data components of Visual Studio 6.0 or can be downloaded from the following Web address: Create and execute the following program. It calls the byroyalty stored procedure in the SQL Server pubs sample database three different ways. It prints the returned recordset on the desktop and displays a wait window between each of the methods. Substitute an appropriate Server, User ID and Password in the definition of the lcConnString variable.

It may be helpful in understanding the Parameters collection to uncomment the call to the ShowParms() function, found immediately before the stored procedure executes. It prints the contents of the Parameters collection on the desktop.

Sample Code

* Begin code.
      * Demonstrates three ways to call a stored procedure that accepts
      * parameters.
      *
      * The stored procedure used is BYROYALTY in pubs, which queries the
      * titleauthor table for royalty amounts that equal the
      * passed value, and returns a recordset.

      #DEFINE adInteger 3
      #DEFINE adParamOutput 2
      #DEFINE adUseClient 3
      #DEFINE adModeReadWrite 3
      #DEFINE adCmdText 1
      #DEFINE adExecuteNoRecords 128

      CLEAR

      oConnection = CREATEOBJECT("ADODB.Connection")
      oCommand = CREATEOBJECT("ADODB.Command")
      oRecordSet = CREATEOBJECT("ADODB.Recordset")
      oParameters = CREATEOBJECT("ADODB.Parameter")

      lcConnString = "driver={SQL Server};" + ;
         "Server=CHICKENHAWK;" + ;
         "DATABASE=pubs"
      lcUID = "sa"
      lcPWD = ""

      WITH oConnection
         .CursorLocation = adUseClient
         .ATTRIBUTES = adModeReadWrite
         .OPEN(lcConnString,lcUID,lcPWD, )
      ENDWITH

      ************************************************************
      * Here's the easiest way to implement:
      *
      * Tell the command object that the CommandType is
      * a regular command, and pass the parameter you want
      * in the CommandText. Most providers can interpret
      * the default adCmdUnknown, or the common adCmdText correctly.
      *
      * However, it will not let you return a value.

      WITH oCommand
         .CommandText = "byroyalty (40)"
         .ActiveConnection = oConnection
      ENDWITH

      oRecordSet.OPEN(oCommand)
      * display the recordset on the desktop
      =ShowRS()
      WAIT WINDOW "Method 1 complete - press a key to continue"

      ** released rs and command object

      release oRecordset
      release oCommand
      
      ************************************************************
      * A second method is to pass parameters with a command,
      * automatically populating the parameters collection.
      *
      * The programmer does not have to know the parameter binding
      * information, the Parameters collection refresh method
      * gets it for you from the server.
      *
      * However, this method has to go to the server
      * before calling the Stored Procedure, resulting in a likely
      * performance hit.
      *
      * This command text string says:
      * Return a parameter (?) from a call to SP byroyalty
      * which accepts one input parameter (?).


      oCommand = CREATEOBJECT("ADODB.Command")
      oRecordSet = CREATEOBJECT("ADODB.Recordset")

      WITH oCommand
         * This command text string says:
         * call SP byroyalty, which accepts one input parameter (?). 

      * removed comment
      * remove ? = here for input parameter

         .CommandText = "{call byroyalty (?)}"
         .ActiveConnection = oConnection
         .PARAMETERS.REFRESH
      ENDWITH

      * Specify the parameter
      oCommand.PARAMETERS(0).VALUE = 40

      oRecordSet = oCommand.Execute
      =ShowRS()
      WAIT WINDOW "Method 2 complete - press a key to continue"

      ** release rs and cmd
      release oCommand
      release oRecordset
      
      ************************************************************
      *
      * A third method to implement it.
      * Create both parameters manually and append them to the
      * parameters collection.
      *
      * The programmer has to know the binding information,
      * but there's no performance hit as with method 2.

      oCommand = CREATEOBJECT("ADODB.Command")
      oRecordSet = CREATEOBJECT("ADODB.Recordset")
      oParameters = CREATEOBJECT("ADODB.Parameter")

      WITH oCommand
         .commandtype = adCmdText 

      * removed ?= here for nonexistent input parm
         .commandtext = "{call byroyalty (?)}"

      * removed definition of input parameter
         .PARAMETERS.APPEND (oCommand.CreateParameter("@percentage",;
            adInteger, 1, 4, 40))
         .ActiveConnection = oConnection
      ENDWITH

      oRecordSet = oCommand.Execute
      =ShowRS()
      WAIT WINDOW "Method 3 complete - press a key to continue"

      * function ShowRs: Print the returned recordset on the desktop.
      FUNCTION ShowRS()
      oRecordSet.MoveFirst
      ? "Records returned: ", oRecordSet.RecordCount
      * and print the au_id field values
      DO WHILE ! oRecordSet.EOF
         ? oRecordSet.FIELDS("au_id").VALUE
      oRecordSet.MoveNext
      ENDDO
      ?
      * End Code
				

The constants used were defined using the Microsoft Visual Basic 6.0 object browser.