Several ways for ASP to call stored procedures with parameters.
Recently, many friends have asked about calling stored procedures. Here is a brief introduction to several ways for ASP to call stored procedures with parameters.
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 and release 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 and release resources
rs.close
connection.close
setrs=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 function return value of C and VB; the other is to return multiple values, and the variable names to store these values need to be specified in the calling parameters first.
This example needs to handle multiple parameters, input parameters, output parameters, return record sets and a direct return value (is it complete enough?)
The stored procedure is as follows:
use pubs
GO
-- Create a stored procedure
create procedure sp_PubsTest
-- define three parameter variables, pay attention to the third one, the special mark is 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 this stored procedure is as follows:
<%@ Language=VBScript %>
<%
Dim CmdSP
DimadoRS
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")
'Create a link
CmdSP.ActiveConnection = "Driver={SQL Server};server=(local);Uid=sa;Pwd=;Database=Pubs"
'Define command object call name
CmdSP.CommandText = "sp_PubsTest"
'Set the command calling type to be a stored procedure (adCmdSPStoredProc = 4)
CmdSP.CommandType = adCmdSPStoredProc
'Add parameters to the command object
'Define the stored procedure to have a direct return value, and it is an integer. The default value is 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, 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 out each record. The fields are virtual and can be ignored.
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>"
'General cleaning
Set adoRS = nothing
Set CmdSP.ActiveConnection = nothing
Set CmdSP = nothing
%>
-------------------------------------------------- -------------------------------------------------- --------------------------------
Calling the stored procedure of sql server in asp can speed up the running speed of the program
1. Call the storage The general method of the process assumes that there is a stored procedure dt_users in sql server:
CREATE PROCEDURE [dbo].[dt_users]
AS
select * from users
return
GO
The first method is not to use the command object, but directly to use the recordset object.
set rs=server.createobject("adodb.recordset")
sql="exec dt_users"
rs.open sql,conn,1,1 so that the second method is to use the command object
set comm=server.createobject("adodb.command")
comm.commantype=4
set comm.activeconnection=conn
comm.commandtext="dbo.dt_users"
set rs=server.createobject("adodb.recordset")
rs.open comm,,1,1
2. Pass parameters to the stored procedure. If there are no parameters in the stored procedure, but a single SQL statement, the advantage of calling the stored procedure will not be shown!
For example, for a bbs query, you can query by author and topic! Then you can create a stored procedure as follows:
The parameter keyword is the keyword, and choose is the method of selecting the query.
CREATE PROCEDURE [dbo].[dt_bbs]
@keyword varchar(20)=null,
@choose int=null
as
if @choose=1
select * from bbs where name like @keyword
else
select * from bbs where subject like @keyword
return
go
In this way, when we call the stored procedure, we only need to pass the parameters, instead of writing a program in asp and use the first method:
set rs=server.createobject("adodb.recordset")
sql="exec dt_bbs '"&keyword&"',"&choose&""
rs.open sql,conn,1,1
Use the second method:
set comm=server.createobject("adodb.command")
comm.commantype=4
comm.Parameters.append comm.CreateParameter("@keyword",adChar,adParamInput,50,keyword)
comm.Parameters.append comm.CreateParameter("@keyword",adInteger,adParamInput,,choose)
set comm.activeconnection=conn
comm.commandtext="dbo.dt_bbs"
set rs=server.createobject("adodb.recordset")
rs.CursorType=3
rs.open comm,,1,1