Several ways to call stored procedures with parameters �
Keywords Stored Procedures
Source
Author: Beggar Cat
Several ways to call stored procedures with parameters
Recently, many friends have asked questions about calling stored procedures. Here are a brief introduction to several methods of ASP calling stored procedures with parameters.
1 This is also the easiest method, with two input parameters, no return value:
set connection = ("")
someDSN
"procname varvalue1, varvalue2"
'Clear all objects into nothing, free up resources
set connection = nothing
2. If you want to return the Recordset set:
set connection = ("")
someDSN
set rs = ("")
"Exec procname varvalue1, varvalue2",connection
'Clear all objects into nothing, free up resources
set rs = nothing
set connection = nothing
3. Neither of the above two methods can have a return value (except Recordset). If you want to get the return value, you need to use the Command method.
First of all, there are two types of return values. One is to directly return a value in the stored procedure, just like the functions of C and VB return values; the other is to return multiple values,
The variable names that store these values need to be specified first in the call parameter.
This example will deal with multiple parameters, input parameters, output parameters, return recordsets and a direct return value (is it all 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 one, the special mark 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 + '%'
--Return 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 there is no 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 = ("")
'Create a link
= "Driver={SQL Server};server=(local);Uid=sa;Pwd=;Database=Pubs"
'Define command Object call name
= "sp_PubsTest"
'Set command call type as a stored procedure (adCmdSPStoredProc = 4)
= adCmdSPStoredProc
'Add parameters to the command object
'Define the stored procedure has a direct return value, and is an integer, the default value is 4
("RETURN_VALUE", adInteger, adParamReturnValue, 4)
'Define a character input parameter
("@au_lname", adVarChar, adParaminput, 20, "M")
'Define an integer input parameter
("@intID", adInteger, adParamInput, , iVal)
'Define an integer output parameter
("@intIDOut", adInteger, adParamOutput, oVal)
'Run the stored procedure and get the return record set
Set adoRS =
'Print out each record, the fields in which are virtual, you can ignore it
While Not
for each adoField in
& "=" & & "<br>" & vbCRLF
Next
"<br>"
Wend
'Print two output values:
"<p>@intIDOut = “ & ("@intIDOut").Value & "</p>"
"<p>Return value = " & ("RETURN_VALUE").Value & "</p>"
'Big cleaning
Set adoRS = nothing
Set = nothing
Set CmdSP = nothing
%>
Asp call database stored procedure �
Keywords asp, database, stored procedures
Source
Asp calls the database stored procedure
<%Set Dataconn = ("")
'Create a connection object
"DSN=SinoTrans;SERVER=APP_SERVER;UID=sa;PWD=;APP=Microsoft(R) Developer Studio;WSID=APP_SERVER;Regional=Yes"
Set cmdTemp = ("")
'Create a command object
Set rst = ("")
'Create a record set object
= "dbo.pd_test" 'Stored procedure name
= 4
'Command category is 4, denoted as stored procedure
Set = Dataconn
Set tmpParam = ("Return Value", 3, 4, 4)
tmpParam
Set tmpParam = ("@BeginDate", 135, 1, 16, riqi)
'Create an input parameter object
tmpParam
cmdTemp, , 1, 3
'Generate query results
%>
The stored procedure called here is pd_test, which is a standard method provided in ADO. However, there is a problem, that is, when there are more than two SELECT statements in the stored procedure, but it is logically impossible to execute at the same time, ADO will prompt you that there are too many 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 = ("")
'Create a connection object
"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 = (ss)
%>
Keywords Stored Procedures
Source
Author: Beggar Cat
Several ways to call stored procedures with parameters
Recently, many friends have asked questions about calling stored procedures. Here are a brief introduction to several methods of ASP calling stored procedures with parameters.
1 This is also the easiest method, with two input parameters, no return value:
set connection = ("")
someDSN
"procname varvalue1, varvalue2"
'Clear all objects into nothing, free up resources
set connection = nothing
2. If you want to return the Recordset set:
set connection = ("")
someDSN
set rs = ("")
"Exec procname varvalue1, varvalue2",connection
'Clear all objects into nothing, free up resources
set rs = nothing
set connection = nothing
3. Neither of the above two methods can have a return value (except Recordset). If you want to get the return value, you need to use the Command method.
First of all, there are two types of return values. One is to directly return a value in the stored procedure, just like the functions of C and VB return values; the other is to return multiple values,
The variable names that store these values need to be specified first in the call parameter.
This example will deal with multiple parameters, input parameters, output parameters, return recordsets and a direct return value (is it all 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 one, the special mark 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 + '%'
--Return 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 there is no 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 = ("")
'Create a link
= "Driver={SQL Server};server=(local);Uid=sa;Pwd=;Database=Pubs"
'Define command Object call name
= "sp_PubsTest"
'Set command call type as a stored procedure (adCmdSPStoredProc = 4)
= adCmdSPStoredProc
'Add parameters to the command object
'Define the stored procedure has a direct return value, and is an integer, the default value is 4
("RETURN_VALUE", adInteger, adParamReturnValue, 4)
'Define a character input parameter
("@au_lname", adVarChar, adParaminput, 20, "M")
'Define an integer input parameter
("@intID", adInteger, adParamInput, , iVal)
'Define an integer output parameter
("@intIDOut", adInteger, adParamOutput, oVal)
'Run the stored procedure and get the return record set
Set adoRS =
'Print out each record, the fields in which are virtual, you can ignore it
While Not
for each adoField in
& "=" & & "<br>" & vbCRLF
Next
"<br>"
Wend
'Print two output values:
"<p>@intIDOut = “ & ("@intIDOut").Value & "</p>"
"<p>Return value = " & ("RETURN_VALUE").Value & "</p>"
'Big cleaning
Set adoRS = nothing
Set = nothing
Set CmdSP = nothing
%>
Asp call database stored procedure �
Keywords asp, database, stored procedures
Source
Asp calls the database stored procedure
<%Set Dataconn = ("")
'Create a connection object
"DSN=SinoTrans;SERVER=APP_SERVER;UID=sa;PWD=;APP=Microsoft(R) Developer Studio;WSID=APP_SERVER;Regional=Yes"
Set cmdTemp = ("")
'Create a command object
Set rst = ("")
'Create a record set object
= "dbo.pd_test" 'Stored procedure name
= 4
'Command category is 4, denoted as stored procedure
Set = Dataconn
Set tmpParam = ("Return Value", 3, 4, 4)
tmpParam
Set tmpParam = ("@BeginDate", 135, 1, 16, riqi)
'Create an input parameter object
tmpParam
cmdTemp, , 1, 3
'Generate query results
%>
The stored procedure called here is pd_test, which is a standard method provided in ADO. However, there is a problem, that is, when there are more than two SELECT statements in the stored procedure, but it is logically impossible to execute at the same time, ADO will prompt you that there are too many 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 = ("")
'Create a connection object
"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 = (ss)
%>