1,调用没有参数的存储过程 <% set conn=server.CreateObject("adodb.connection") set cmd=server.CreateObject("adodb.command") strconn="dsn=pubs;uid=sa;pwd"conn.Open strconn set cmd.ActiveConnection=conncmd.CommandText="{call nono}"''set rs=cmc.exe 或者cmd.executeset rs=cmd.Execute()%> 2,一个输入的参数的存储过程 <% set conn=server.CreateObject("adodb.connection") set cmd=server.CreateObject("adodb.command") strconn="dsn=pubs;uid=sa;pwd"conn.Open strconn set cmd.ActiveConnection=conncmd.CommandText="{call oneinput(?)}" cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger ,adParamInput ) cmd("@aaa")=100cmd.Execute()%> 3,一个输入参数和一个输出的参数 <% set conn=server.CreateObject("adodb.connection") set cmd=server.CreateObject("adodb.command") strconn="dsn=pubs;uid=sa;pwd"conn.Open strconn set cmd.ActiveConnection=conncmd.CommandText = "{call oneinout(?,?)}" cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger,adParamInput) cmd("@aaa")=10 cmd.Parameters.Append cmd.CreateParameter("@bbb",adInteger,adParamOutput)cmd.Execute()bbb=cmd("@bbb") %> 4,一个输入参数,一个输出参数,和一个返回值 <% set conn=server.CreateObject("adodb.connection") set cmd=server.CreateObject("adodb.command") strconn="dsn=pubs;uid=sa;pwd"conn.Open strconn set cmd.ActiveConnection=conncmd.CommandText="{?=call onereturn(?,?)}"cmd.Parameters.Append cmd.CreateParameter("@return_value",adInteger,adParamReturnValue ) cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger,adParamInput ) cmd("@aaa")=10 cmd.Parameters.Append cmd.CreateParameter("@bbb",adInteger,adParamOutput)cmd.Execute()bbb=cmd("@bbb") rrr=cmd("@return_value") %> Dim mCnn As ADODB.Connection Dim mRst As ADODB.Recordset Dim mCmd As ADODB.Command Dim mPrm As ADODB.ParameterDim mstrCnn As String Dim mstrCmd As StringPrivate Sub Form_Load() mstrCnn = _ "Provider=SQLOLEDB.1;" & _ "Persist Security Info=False;" & _ "User ID=sa;" & _ "Password=sa;" & _ "Initial Catalog=pubs;" & _ "Data Source=(local)"
On Error GoTo ErrHandler
Set mCnn = New ADODB.Connection mCnn.ConnectionString = mstrCnn mCnn.Open
Set mCmd = New ADODB.Command mCmd.CommandText = "ProcRet" mCmd.CommandType = adCmdStoredProc mCmd.ActiveConnection = mCnn
Set mPrm = New ADODB.Parameter mPrm.Name = "RecCnt" mPrm.Type = adInteger mPrm.Direction = adParamOutput mCmd.Parameters.Append mPrm
Debug.Print "BEGIN" & String$(40, "-")
Set mRst = mCmd.Execute
The following stored procedure contains one input parameter, one output parameter, and a return parameter. The procedure selects those rows in the titles table of the pubs database where the royalty percent paid to the author is greater than the amount entered by the user (the input parameter). The program returns the number of rows as the output variable. If the program returns any rows, a return code of 0 is issued; if no rows are returned, a return code of 99 is issued.USE pubs GO CREATE PROCEDURE myProc @outparm int OUTPUT @inparm int AS SELECT * FROM titles WHERE royalty > @inparm SELECT @outparm = COUNT (*) FROM TITLES WHERE royalty > @inparm IF (@outparm > 0) RETURN 0 ELSE RETURN 99 GOAn ADO code program that executes the stored procedure myProc is shown here.Dim cn As New ADODB.Connection Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset Dim fldloop As ADODB.Field Dim param1 As Parameter, param2 As Parameter, param3 As Parameter Dim provStr As String Dim royalty As Variant
Private Sub spStart()' Connect using the SQLOLEDB provider. cn.Provider = "sqloledb"' Specify connection string on Open method. provStr = "Server=MyServer;Database=pubs;Trusted_Connection=yes" cn.Open provStr' Set up a command object for the stored procedure. Set cmd.ActiveConnection = cn cmd.CommandText = "myProc" cmd.CommandType = adCmdStoredProc' Set up a return parameter. Set param1 = cmd.CreateParameter("Return", adInteger, adParamReturnValue) cmd.Parameters.Append param1
' Set up an output parameter. Set param2 = cmd.CreateParameter("Output", adInteger, adParamOutput) cmd.Parameters.Append param2
' Set up an input parameter. Set param3 = cmd.CreateParameter("Input", adInteger, adParamInput) cmd.Parameters.Append param3 royalty = Trim(InputBox("Enter royalty:")) param3.Value = royalty' Execute command, and loop through recordset, printing out rows. Set rs = cmd.ExecuteDim i As Integer While Not rs.EOF For Each fldloop In rs.Fields Debug.Print rs.Fields(i) i = i + 1 Next fldloop Debug.Print "" i = 0 rs.MoveNext Wend' Need to close recordset before getting return ' and output parameters. rs.CloseDebug.Print "Program ended with return code: " & Cmd(0) Debug.Print "Total rows satisfying condition: " & Cmd(1) cn.CloseEnd Sub
1,调用没有参数的存储过程
<%
set conn=server.CreateObject("adodb.connection")
set cmd=server.CreateObject("adodb.command")
strconn="dsn=pubs;uid=sa;pwd"conn.Open strconn
set cmd.ActiveConnection=conncmd.CommandText="{call nono}"''set rs=cmc.exe 或者cmd.executeset rs=cmd.Execute()%>
2,一个输入的参数的存储过程
<%
set conn=server.CreateObject("adodb.connection")
set cmd=server.CreateObject("adodb.command")
strconn="dsn=pubs;uid=sa;pwd"conn.Open strconn
set cmd.ActiveConnection=conncmd.CommandText="{call oneinput(?)}"
cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger ,adParamInput )
cmd("@aaa")=100cmd.Execute()%>
3,一个输入参数和一个输出的参数
<%
set conn=server.CreateObject("adodb.connection")
set cmd=server.CreateObject("adodb.command")
strconn="dsn=pubs;uid=sa;pwd"conn.Open strconn
set cmd.ActiveConnection=conncmd.CommandText = "{call oneinout(?,?)}"
cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger,adParamInput)
cmd("@aaa")=10
cmd.Parameters.Append cmd.CreateParameter("@bbb",adInteger,adParamOutput)cmd.Execute()bbb=cmd("@bbb")
%>
4,一个输入参数,一个输出参数,和一个返回值
<%
set conn=server.CreateObject("adodb.connection")
set cmd=server.CreateObject("adodb.command")
strconn="dsn=pubs;uid=sa;pwd"conn.Open strconn
set cmd.ActiveConnection=conncmd.CommandText="{?=call onereturn(?,?)}"cmd.Parameters.Append cmd.CreateParameter("@return_value",adInteger,adParamReturnValue )
cmd.Parameters.Append cmd.CreateParameter("@aaa",adInteger,adParamInput )
cmd("@aaa")=10
cmd.Parameters.Append cmd.CreateParameter("@bbb",adInteger,adParamOutput)cmd.Execute()bbb=cmd("@bbb")
rrr=cmd("@return_value")
%> Dim mCnn As ADODB.Connection
Dim mRst As ADODB.Recordset
Dim mCmd As ADODB.Command
Dim mPrm As ADODB.ParameterDim mstrCnn As String
Dim mstrCmd As StringPrivate Sub Form_Load() mstrCnn = _
"Provider=SQLOLEDB.1;" & _
"Persist Security Info=False;" & _
"User ID=sa;" & _
"Password=sa;" & _
"Initial Catalog=pubs;" & _
"Data Source=(local)"
On Error GoTo ErrHandler
Set mCnn = New ADODB.Connection
mCnn.ConnectionString = mstrCnn
mCnn.Open
Set mCmd = New ADODB.Command
mCmd.CommandText = "ProcRet"
mCmd.CommandType = adCmdStoredProc
mCmd.ActiveConnection = mCnn
Set mPrm = New ADODB.Parameter
mPrm.Name = "RecCnt"
mPrm.Type = adInteger
mPrm.Direction = adParamOutput
mCmd.Parameters.Append mPrm
Debug.Print "BEGIN" & String$(40, "-")
Set mRst = mCmd.Execute
The following stored procedure contains one input parameter, one output parameter, and a return parameter. The procedure selects those rows in the titles table of the pubs database where the royalty percent paid to the author is greater than the amount entered by the user (the input parameter). The program returns the number of rows as the output variable. If the program returns any rows, a return code of 0 is issued; if no rows are returned, a return code of 99 is issued.USE pubs
GO
CREATE PROCEDURE myProc
@outparm int OUTPUT
@inparm int
AS
SELECT * FROM titles WHERE royalty > @inparm
SELECT @outparm = COUNT (*) FROM TITLES WHERE royalty > @inparm
IF (@outparm > 0)
RETURN 0
ELSE
RETURN 99
GOAn ADO code program that executes the stored procedure myProc is shown here.Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim fldloop As ADODB.Field
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter
Dim provStr As String
Dim royalty As Variant
Private Sub spStart()' Connect using the SQLOLEDB provider.
cn.Provider = "sqloledb"' Specify connection string on Open method.
provStr = "Server=MyServer;Database=pubs;Trusted_Connection=yes"
cn.Open provStr' Set up a command object for the stored procedure.
Set cmd.ActiveConnection = cn
cmd.CommandText = "myProc"
cmd.CommandType = adCmdStoredProc' Set up a return parameter.
Set param1 = cmd.CreateParameter("Return", adInteger, adParamReturnValue)
cmd.Parameters.Append param1
' Set up an output parameter.
Set param2 = cmd.CreateParameter("Output", adInteger, adParamOutput)
cmd.Parameters.Append param2
' Set up an input parameter.
Set param3 = cmd.CreateParameter("Input", adInteger, adParamInput)
cmd.Parameters.Append param3
royalty = Trim(InputBox("Enter royalty:"))
param3.Value = royalty' Execute command, and loop through recordset, printing out rows.
Set rs = cmd.ExecuteDim i As Integer
While Not rs.EOF
For Each fldloop In rs.Fields
Debug.Print rs.Fields(i)
i = i + 1
Next fldloop
Debug.Print ""
i = 0
rs.MoveNext
Wend' Need to close recordset before getting return
' and output parameters.
rs.CloseDebug.Print "Program ended with return code: " & Cmd(0)
Debug.Print "Total rows satisfying condition: " & Cmd(1)
cn.CloseEnd Sub