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=("")
sql="exec dt_users"
sql,conn,1,1 That's it
The second method is to use the command object
set comm=("")
=4
set =conn
="dbo.dt_users"
set rs=("")
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=("")
sql="exec dt_bbs '"&keyword&"',"&choose&""
sql,conn,1,1
Use the second method:
set comm=("")
=4
("@keyword",adChar,adParamInput,50,keyword)
("@keyword",adInteger,adParamInput,,choose)
set =conn
="dbo.dt_bbs"
set rs=("")
=3
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:,, these 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 not the same. Using stored procedures can make the program more modular, easy to modify and debug (you can directly debug under SQL Server, without looking at the results of Asp under IE).
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=("")
sql="exec dt_users"
sql,conn,1,1 That's it
The second method is to use the command object
set comm=("")
=4
set =conn
="dbo.dt_users"
set rs=("")
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=("")
sql="exec dt_bbs '"&keyword&"',"&choose&""
sql,conn,1,1
Use the second method:
set comm=("")
=4
("@keyword",adChar,adParamInput,50,keyword)
("@keyword",adInteger,adParamInput,,choose)
set =conn
="dbo.dt_bbs"
set rs=("")
=3
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:,, these 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 not the same. Using stored procedures can make the program more modular, easy to modify and debug (you can directly debug under SQL Server, without looking at the results of Asp under IE).