HOWTO: Call a Parameterized SQL Server Stored Procedure from ADOID: Q195047
|
The main purpose of the ActiveX Data Objects (ADO) Command object is to allow the execution of stored procedures and parameterized commands against a data provider.
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:
http://www.microsoft.com/data/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.
* 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
CLEAR
oConnection = CREATEOBJECT("ADODB.Connection")
oCommand = CREATEOBJECT("ADODB.Command")
oRecordSet = CREATEOBJECT("ADODB.Recordset")
oParameters = CREATEOBJECT("ADODB.Parameter")
lcConnString = "driver={SQL Server};" + ;
"Server=YourServerName;" + ;
"DATABASE=pubs"
lcUID = "YourUserID"
lcPWD = "YourPassword"
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"
************************************************************
* 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 (?).
WITH oCommand
* This command text string says:
* Return a parameter (?) from a call to SP byroyalty
* which accepts one input parameter (?). This particular
* SP does not return a value, so this is for illustration
* only.
.CommandText = "{? = call byroyalty (?)}"
.ActiveConnection = oConnection
.PARAMETERS.REFRESH
ENDWITH
* Specify the parameter
* the 0th parameter will be the return value, if any.
oCommand.PARAMETERS(1).VALUE = 40
* Uncomment this to see the contents of the parameters collection.
* =ShowParms()
oRecordSet = oCommand.Execute
=ShowRS()
WAIT WINDOW "Method 2 complete - press a key to continue"
************************************************************
*
* 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.
WITH oCommand
.commandtype = adCmdText
.commandtext = "{? = call byroyalty (?)}"
* Must create and append separate parameters for the Return Value
* and the passed @percentage.
.PARAMETERS.APPEND (oCommand.CreateParameter("RETURN_VALUE",;
adInteger, 4, 4))
.PARAMETERS.APPEND (oCommand.CreateParameter("@percentage",;
adInteger, 1, 4, 40))
.ActiveConnection = oConnection
ENDWITH
* Uncomment this to display the contents of the Parameters collection.
* =ShowParms()
oRecordSet = oCommand.Execute
=ShowRS()
WAIT WINDOW "Method 3 complete - press a key to continue"
* function ShowParms: Print the contents of the parameters collection
* on the desktop.
FUNCTION ShowParms()
* Display the contents of the Parameters collection
* of the command object.
CLEAR
FOR i = 0 TO oCommand.PARAMETERS.COUNT - 1
? "Element: ", i
? "Attributes: ", oCommand.PARAMETERS(i).ATTRIBUTES
? "Direction: ", oCommand.PARAMETERS(i).Direction
? "Name: ", oCommand.PARAMETERS(i).NAME
? "NumericScale: ", oCommand.PARAMETERS(i).NumericScale
? "Precision: ", oCommand.PARAMETERS(i).PRECISION
? "Size: ", oCommand.PARAMETERS(i).SIZE
? "Type: ", oCommand.PARAMETERS(i).TYPE
? "Value: ", oCommand.PARAMETERS(i).VALUE
?
NEXT
* 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 MSDN Visual Studio Library 6.0; topic: "ADO Detailed Programming Model" topic
Additional query words: Query
Keywords : kbActiveX kbADO200 kbDatabase kbMDAC kbSQL kbVFp kbVFp600
Version : WINDOWS:6.0
Platform : WINDOWS
Issue type : kbhowto Last Reviewed: July 28, 1999