您目前已離線,請等候您的網際網路重新連線

不支援您的瀏覽器

您必須更新瀏覽器,才能使用此網站。

更新至最新版本的 Internet Explorer

如何從 ASP 呼叫 SQL Server 預存程序

依現狀不再更新的知識庫內容免責聲明
本文旨在說明 Microsoft 不再提供支援的產品。因此,本文係依「現狀」提供,不會再更新。
結論
如需本文的 Microsoft Visual Basic .NET 版本,請參閱 306574
本文將告訴您,使用 ActiveX Data Objects (ADO) 從 Active Server Page 呼叫預存程序的三種方法。
其他相關資訊
下列範例使用 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 物件的預設集合,而其他方法指定要存取的特定集合的屬性。
参考
如需有關 Visual InterDev 和 Active Server Page 的最新知識庫文件及其他支援資訊,請參閱 Microsoft 技術支援網站的下列網頁:
內容

文章識別碼:164485 - 最後檢閱時間:08/18/2006 11:41:06 - 修訂: 4.0

  • Microsoft Active Server Pages 4.0
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 2.1 Service Pack 1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 2
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft ActiveX Data Objects 2.6
  • Microsoft ActiveX Data Objects 2.7
  • kbcode kbdatabase kbhowto kbsample kbscript KB164485
意見反應
rc="https://c.microsoft.com/ms.js">