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

liu_jinbo 2006-08-25

展开全文

如何从 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" %>
<!--#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 对象的默认集合，
而其他方法指定了要访问的特定集合的属性。
 



这篇文章中的信息适用于:

    
        
            •
            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