Call the stored procedure of SQL Server in ASP

Calling the stored procedure of SQL Server in ASP can be accelerated Program Therefore, the SQL server stored procedure has been used in ASP over the past few days. Many of the ideas are also obtained directly from the chinaasp Forum, hoping to help you.

1. General method for calling a stored procedure
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 method is to directly use the recordset object without using the command object.
Set rs = server. Createobject ("ADODB. recordset ")
SQL = "Exec dt_users"
Rs. Open SQL, Conn ,.
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 a single SQL statement is not used in the stored procedure, the advantages of calling the stored procedure are not displayed!
for example, a BBS query can be queried by author or topic! You can create a stored procedure as follows:
the keyword parameter is the keyword, and choose is the method for selecting to 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
this way, when we call a stored procedure, we only need to pass the parameter over, however, you do not need to write a program in ASP.
the first method is used:
set rs = server. createobject ("ADODB. recordset ")
SQL =" Exec dt_bbs "& keyword &" ', "& choose &" "
Rs. open SQL, Conn, 1, 1
Method 2:
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

3. further discussion
compare the two methods mentioned above to call stored procedures in ASP.
the first method requires fewer objects, however, the recordset object supports fewer attributes, such as Rs. recordcount, Rs. pagecount, Rs. absolutepage attributes
are not supported, so the first method is limited. For example, the second method must be used to display records by page.
we use the stored procedure in SQL Server to accelerate the speed. However, when there are many SQL statements in a stored procedure, its advantages are particularly obvious. If there are not many SQL statements,
but we must create one more command object in the second method, which may slow down the speed! Therefore, we need to balance the interests of all parties to use the stored procedure.
however, I don't think the speed is good. Using Stored Procedures can make the program more modular, easy to modify, and debug (you can directly debug it under SQL Server, instead of viewing ASP
results in IE ).