Do we often reflect on some issues when we learn asp to call stored procedures? For example, how to operate the method of calling stored procedures? What are the parameters passing to stored procedures, etc. Today, the new technology channel of the bug will take you to the following article to learn these knowledge. I hope it will be helpful to your learning!
1. General methods of calling stored procedures
First, let's assume that there is a stored procedure dt_users in the SQL server:
CREATE PROCEDURE [dbo].[dt_users]
AS
select * from users
Return
GO
The first method is to use the recordset object without using the command object,
set rs=server.createobject("adodb.recordset")
sql="exec dt_users"
rs.open sql,conn,1,1
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 stored procedures
If there are no parameters in the stored procedure, but a single SQL statement, the advantages of calling stored procedures cannot be shown!
For example, a bbs query can be queried by the author and topic! Then the stored procedure can be established as follows:
The parameter keyword is the keyword, and choose is the method to select 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 stored procedures, we just need to pass the parameters over, and omit writing a program in asp
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
3. Further discussion
Comparing the two methods I mentioned above to call stored procedures in asp,
The first method requires fewer objects, but the recordset object supports much fewer properties, such as: rs.recordcount, rs.pagecount, rs.absolutepage and other properties.
None of them supports it, so there are limitations to using the first method. For example, when you want to display records on pages, you must use the second method.
The purpose of using stored procedures in SQL Server is to speed up, but when there are many SQL statements in a stored procedure, its advantages are particularly obvious. If there are not many SQL statements,
When we use the second method, we must create an additional command object, which may slow down! So we must balance the interests of all aspects to use stored procedures.
However, I think the speed is so good. Using stored procedures can make the program more modular, easy to modify and debug (you can directly debug in SQL Server without looking at the results of Asp under IE).
The above is an introduction to the stored procedures for asp calls. I hope it will be helpful for you to learn this knowledge. I also hope that everyone will continue to support the wrong new technology channel!