Invoking SQL Server's stored procedures in ASP can speed up the program, so these days in the study of ASP in the use of SQL Server stored procedures, are summarized as follows, many of these ideas are directly from the chinaasp forum, hoping to give you some help.
1. General method of calling stored procedures
Let's assume that there is a stored procedure dt_users in SQL Server:
CREATE PROCEDURE [dbo]. [Dt_users]
As
SELECT * from Users
Return
Go
The first approach is to not use the command object, directly using the Recordset object
Set Rs=server.createobject ("Adodb.recordset")
Sql= "Exec dt_users"
Rs.Open sql,conn,1,1 so you can
The second approach is to take advantage of 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. Passing parameters to stored procedures
If you do not use parameters in stored procedures, but a single SQL statement, you also do not show the advantage of invoking the stored procedure!
For example, a BBS query, according to the author and subject inquiry! You can establish a stored procedure as follows:
parameter keyword is a keyword, choose is the way to select a query.
CREATE PROCEDURE [dbo]. [Dt_bbs]
@keyword varchar (=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
So when we call the stored procedure, we simply pass the parameters to the past and omit to write a program in the ASP.
Use the first method:
Set Rs=server.createobject ("Adodb.recordset")
sql= "exec Dt_bbs '" &keyword& "", "&choose&" "
Rs.Open sql,conn,1,1
In 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"
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.