下面的示例使用 Command 对象调用示例存储过程 sp_test。此存储过程接受整数,同时返回一个整数值:
<%@ LANGUAGE="VBSCRIPT" %> <!--#include virtual="/ASPSAMP/SAMPLES/ADOVBS.INC"--> <HTML> <HEAD><TITLE>Place Document Title Here</TITLE></HEAD> <BODY> This first method queries the data source about the parameters of the stored procedure. This is the least efficient method of calling a stored procedure.<BR> <% Set cn = Server.CreateObject("ADODB.Connection") Set cmd = Server.CreateObject("ADODB.Command") cn.Open "data source name", "userid", "password" Set cmd.ActiveConnection = cn cmd.CommandText = "sp_test" cmd.CommandType = adCmdStoredProc ' Ask the server about the parameters for the stored proc cmd.Parameters.Refresh ' Assign a value to the 2nd parameter. ' Index of 0 represents first parameter. cmd.Parameters(1) = 11 cmd.Execute %> Calling via method 1<BR> ReturnValue = <% Response.Write cmd.Parameters(0) %><P> <!-- ************************************************************ --> Method 2 declares the stored procedure, and then explicitly declares the parameters.<BR> <% Set cn = Server.CreateObject("ADODB.Connection") cn.Open "data source name", "userid", "password" Set cmd = Server.CreateObject("ADODB.Command") Set cmd.ActiveConnection = cn cmd.CommandText = "sp_test" cmd.CommandType = adCmdStoredProc cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, _ adParamReturnValue) cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, _ adParamInput) ' Set value of Param1 of the default collection to 22 cmd("Param1") = 22 cmd.Execute %> Calling via method 2<BR> ReturnValue = <% Response.Write cmd(0) %><P> <!-- ************************************************************ --> Method 3 is probably the most formal way of calling a stored procedure. It uses the canocial <% Set cn = Server.CreateObject("ADODB.Connection") cn.Open "data source name", "userid", "password" Set cmd = Server.CreateObject("ADODB.Command") Set cmd.ActiveConnection = cn ' Define the stored procedure's inputs and outputs ' Question marks act as placeholders for each parameter for the ' stored procedure cmd.CommandText = "{?=call sp_test(?)}" ' specify parameter info 1 by 1 in the order of the question marks ' specified when we defined the stored procedure cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, _ adParamReturnValue) cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, _ adParamInput) cmd.Parameters("Param1") = 33 cmd.Execute %> Calling via method 3<BR> ReturnValue = <% Response.Write cmd("RetVal") %><P> </BODY> </HTML>
请注意,上面的示例使用了访问 Command 对象的 Parameters 集合的各种方法。有些方法使用 Command 对象的默认集合,而其他方法指定了要访问的特定集合的属性。