Calling SQL Server stored procedures in ASP

Source: Internet
Author: User
Tags advantage
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"
Related Article

Contact Us

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.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.