Several Methods for ASP to call stored procedures with Parameters

Several Methods for ASP to call stored procedures with parameters are selected from hxfwsk blogs.
Keyword Stored Procedure
Source

Author: puppet

Several Methods for ASP to call stored procedures with Parameters

Recently, many users have asked questions about calling stored procedures. Here we will briefly introduce several methods for calling stored procedures with Parameters Using ASP.

1. This is also the simplest method. Two input parameters have no return value:
Set connection = server. createobject ("adodb. connection ")
Connection. open someDSN
Connection. Execute "procname varvalue1, varvalue2"

'Clear all objects as nothing and release resources
Connection. close
Set connection = nothing

2. If you want to return the Recordset:
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 as nothing and release resources
Rs. close
Connection. close
Set rs = nothing
Set connection = nothing

3 none of the above two methods can return values (except for Recordset). To obtain the return value, use the Command method.
First, there are two return values. One is to directly return a value in the stored procedure, just like the return values of C and VB functions; the other is to return multiple values.
The variable names for storing these values must be specified in the call parameters first.
In this example, we need to process multiple parameters, input parameters, output parameters, return record sets, and a direct return value (enough ?)
The stored procedure is as follows:

Use pubs
GO

-- Create a stored procedure
Create procedure sp_PubsTest

-- Define three parameter variables. Note that the third parameter 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 calls 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 they are not predefined in VBScript.
AdCmdSPStoredProc = 4
AdParamReturnValue = 4
AdParaminput = 1
AdParamOutput = 2
AdInteger = 3
AdVarChar = 200

IVal = 5
OVal = 3

'Create a command object
Set CmdSP = Server. CreateObject ("ADODB. Command ")

'Establish a connection
CmdSP. ActiveConnection = "Driver = {SQL Server}; server = (local); Uid = sa; Pwd =; Database = Pubs"

'Define the name of the command object call
CmdSP. CommandText = "sp_PubsTest"

'Set the command call type to stored procedure (admo-spstoredproc = 4)
CmdSP. CommandType = adw.spstoredproc

'Add parameters to the command object
'The stored procedure has a direct return value and is an integer. The missing value is 4.
CmdSP. Parameters. Append CmdSP. CreateParameter ("RETURN_VALUE", adInteger, adParamReturnValue, 4)
'Define a struct input parameter
CmdSP. Parameters. Append CmdSP. CreateParameter ("@ au_lname", adVarChar, adParaminput, 20, "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 returned record set
Set adoRS = CmdSP. Execute

'Print each record. The fields in the record are virtual.
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
%>

Asp calls the database Stored Procedure and selects the Blog from 11830
Keywords: asp, database, stored procedure
Source

Asp calls database Stored Procedures

<% Set Dataconn = Server. CreateObject ("ADODB. Connection ")
'Establish a connection object
Dataconn. Open "DSN = SinoTrans; SERVER = APP_SERVER; UID = sa; PWD =; APP = Microsoft (R) Developer Studio; WSID = APP_SERVER; Regional = Yes"
Set parameters temp = Server. CreateObject ("ADODB. Command ")
'Create command object
Set rst = Server. CreateObject ("ADODB. Recordset ")
'Create record set object
Export temp. CommandText = "dbo. pd_test" 'stored procedure name
Optional temp. CommandType = 4
'COMMAND category is 4, which indicates a stored procedure
Set consumer temp. ActiveConnection = Dataconn
Set tmpParam = gradient temp. CreateParameter ("Return Value", 3, 4, 4)
Using temp. Parameters. Append tmpParam
Set tmpParam = gradient temp. CreateParameter ("@ BeginDate", 135, 1, 16, riqi)
'Create input parameter object
Using temp. Parameters. Append tmpParam
Rst. Open keys temp, 1, 3
'Query result generation
%>
The stored procedure called here is pd_test, which is the standard method provided in ADO, but there is a problem that when there are more than two SELECT statements in the stored procedure, but logically, it is impossible for ADO to EXECUTE multiple SELECT statements in the stored procedure. The solution is to directly EXECUTE the stored procedure using the EXECUTE method of the CONNECTION object of ADO, as follows:
<%
Set Dataconn = Server. CreateObject ("ADODB. Connection ")
'Establish a connection object
Dataconn. Open "DSN = SinoTrans; SERVER = APP_SERVER; UID = sa; PWD =; APP = Microsoft (R) Developer Studio; WSID = APP_SERVER; Regional = Yes"
Ss = "EXECUTE dbo. pd_test" & "'" & riqi1 &"'"
Set rs = dataconn. Execute (ss)
%>