Several ways to invoke a stored procedure in ASP

Stored Procedure 1 This is also the simplest method, with two input parameters and no return value:
Set connection = Server.CreateObject ("Adodb.connection")
Connection.Open SOMEDSN
Connection.Execute "ProcName varvalue1, Varvalue2"

' Clear all objects to nothing, releasing resources
Connection.close
Set connection = Nothing

2 If you want to return the recordset set:
Set connection = Server.CreateObject ("Adodb.connection")
Connection.Open SOMEDSN
Set rs = Server.CreateObject ("Adodb.recordset")
Rs. Open "Exec procname varvalue1, varvalue2", connection

' Clear all objects to nothing, releasing resources
Rs.close
Connection.close
Set rs = Nothing
Set connection = Nothing

3 Neither of these methods has a return value (except the Recordset), and the command method is required if the return value is to be obtained.
First, there are two types of return values. One is to return a value directly in the stored procedure, just like the functions returned by C and VB; the other is that you can return multiple values, and the name of the variable that stores the values needs to be specified in the invocation parameter.
This example handles a variety of parameters, input parameters, output parameters, returns a Recordset and a direct return value (enough?). ）
The stored procedure is as follows:

Use pubs
Go

--Establish a stored procedure
CREATE PROCEDURE Sp_pubstest

--Define three parameter variables, note the third, the special tag is used for output
@au_lname varchar (20),
@intID int,
@intIDOut int OUTPUT

As

SELECT @intIDOut = @intID + 1

SELECT *
From authors
WHERE au_lname like @au_lname + '% '

--Returns a value directly
return @intID + 2

The ASP program that invokes the stored procedure is as follows:

<%@ Language=vbscript%>
<%
Dim CMDSP
Dim adors
Dim Adcmdspstoredproc
Dim adParamReturnValue
Dim adParamInput
Dim adParamOutput
Dim Adinteger
Dim ival
Dim OVal
Dim Adofield
Dim adVarChar

' These values are predefined constants in VB and can be called directly, but are not predefined in VBScript
Adcmdspstoredproc = 4
adParamReturnValue = 4
adParamInput = 1
adParamOutput = 2
Adinteger = 3
adVarChar = 200

Ival = 5
OVal = 3

' Build a Command object
Set cmdsp = Server.CreateObject ("Adodb.command")

' Establish a link
Cmdsp.activeconnection = "Driver={sql server};server= (local); Uid=sa; pwd=;D Atabase=pubs "

' Define Command object invocation name
Cmdsp.commandtext = "Sp_pubstest"

' Set command call type is stored procedure (Adcmdspstoredproc = 4)
Cmdsp.commandtype = Adcmdspstoredproc

' Add parameters to the Command object
' Defines a stored procedure that has a direct return value and is an integer, with a missing value of 4
CmdSP.Parameters.Append cmdsp.createparameter ("Return_value", Adinteger, adParamReturnValue, 4)
' Define a character input parameter
CmdSP.Parameters.Append cmdsp.createparameter ("@au_lname", adVarChar, adParamInput, "M")
' Define an integer input parameter
CmdSP.Parameters.Append cmdsp.createparameter ("@intID", Adinteger, adParamInput, ival)
' Define an integer output parameter
CmdSP.Parameters.Append cmdsp.createparameter ("@intIDOut", Adinteger, adParamOutput, OVal)

' Run the stored procedure and get the return recordset
Set adors = Cmdsp.execute

' Print out each record, where the fields are virtual and can be left out of the tube
While not adors.eof

For each Adofield in Adors.fields
Response.Write Adofield.name & "=" & Adofield.value & "<br>" & VbCRLF
Next
Response.Write "<br>"
Adors.movenext
Wend

' Print two output values:
Response.Write "<p> @intIDOut =" & Cmdsp.parameters ("@intIDOut"). Value & "</p>"
Response.Write "<p>return value =" & Cmdsp.parameters ("Return_value"). Value & "</p>"

' Cleaning
Set adors = Nothing
Set cmdsp.activeconnection = Nothing
Set CMDSP = Nothing
%>

In addition, there are other ways, a little bit of the door, and then slowly say
This article refers to a number of articles, not listed here.