神马文学网如何从 ASP 调用 SQL Server 存储过程
来源：百度文库 编辑：神马文学网 时间：2024/04/29 11:52:34
null有关本文的 Microsoft Visual Basic .NET 版本，请参阅306574 (http://support.microsoft.com/kb/306574/)。
本文演示了使用 ActiveX 数据对象 (ADO) 从 Active Server Pages 调用存储过程的三种方法。
更多信息
null下面的示例使用 Command 对象调用示例存储过程 sp_test。此存储过程接受整数，同时返回一个整数值：
<%@ LANGUAGE="VBSCRIPT" %>
<%Set cn = Server.CreateObject("ADODB.Connection")Set cmd = Server.CreateObject("ADODB.Command")cn.Open "data source name", "userid", "password"Set cmd.ActiveConnection = cncmd.CommandText = "sp_test"cmd.CommandType = adCmdStoredProc‘ Ask the server about the parameters for the stored proccmd.Parameters.Refresh‘ Assign a value to the 2nd parameter.‘ Index of 0 represents first parameter.cmd.Parameters(1) = 11cmd.Execute%>Calling via method 1
ReturnValue = <% Response.Write cmd.Parameters(0) %>
Method 2 declares the stored procedure, and then explicitly declaresthe parameters.
<%Set cn = Server.CreateObject("ADODB.Connection")cn.Open "data source name", "userid", "password"Set cmd = Server.CreateObject("ADODB.Command")Set cmd.ActiveConnection = cncmd.CommandText = "sp_test"cmd.CommandType = adCmdStoredProccmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, _adParamReturnValue)cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, _adParamInput)‘ Set value of Param1 of the default collection to 22cmd("Param1") = 22cmd.Execute%>Calling via method 2
ReturnValue = <% Response.Write cmd(0) %>
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 procedurecmd.CommandText = "{?=call sp_test(?)}"‘ specify parameter info 1 by 1 in the order of the question marks‘ specified when we defined the stored procedurecmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, _adParamReturnValue)cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, _adParamInput)cmd.Parameters("Param1") = 33cmd.Execute%>Calling via method 3
ReturnValue = <% Response.Write cmd("RetVal") %>
请注意，上面的示例使用了访问 Command 对象的 Parameters 集合的各种方法。有些方法使用 Command 对象的默认集合，
而其他方法指定了要访问的特定集合的属性。
这篇文章中的信息适用于:
• Microsoft Active Server Pages 4.0
• Microsoft ActiveX Data Objects 2.0
• Microsoft ActiveX Data Objects 2.7
• Microsoft ActiveX Data Objects 2.7
• Microsoft ActiveX Data Objects 2.5
• Microsoft ActiveX Data Objects 2.6
• Microsoft ActiveX Data Objects 2.7