Several ways to invoke a stored procedure in ASP

1 This is also the simplest method, two input parameters, no return value:

Set connection = Server.createob ject ("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.createob ject ("Adodb.connection")

Connection.Open SOMEDSN

Set rs = Server.createob ject ("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=vb SC ript%>

<%

Dim CMDSP

Dim adors

Dim Adcmdspstoredproc

Dim adParamReturnValue

Dim Adparamin put

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 VB SC Ript

Adcmdspstoredproc = 4

adParamReturnValue = 4

Adparamin put = 1

adParamOutput = 2

Adinteger = 3

adVarChar = 200

Ival = 5

OVal = 3

' Build a Command object

Set cmdsp = Server.createob ject ("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, Adparamin put, "M")

' Define an integer input parameter

CmdSP.Parameters.Append cmdsp.createparameter ("@intID", adinteger, Adparamin put, 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

%>