如何从 ASP 调用 SQL Server 存储过程

概要

null

有关本文的 Microsoft Visual Basic .NET 版本，请参阅 306574 (http://support.microsoft.com/kb/306574/)。

本文演示了使用 ActiveX 数据对象 (ADO) 从 Active Server Pages 调用存储过程的三种方法。

更多信息

null

下面的示例使用 Command 对象调用示例存储过程 sp_test。此存储过程接受整数，同时返回一个整数值：

<%@ LANGUAGE="VBSCRIPT" %><HTML><HEAD><TITLE>Place Document Title Here</TITLE></HEAD><BODY>This first method queries the data source about the parametersof the stored procedure. This is the least efficient method of callinga stored procedure. <%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") %></BODY></HTML>

请注意，上面的示例使用了访问 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

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。

•	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