The stored procedure for calling SQL Server in the ASP can speed up the program running speed, so these days are studying the storage procedures of SQL Server in the ASP, and now, many of them are directly from the Chinaasp forum, I hope I can give you some help.
1. General Method for calling the stored procedure
First 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 not to use the Command object, directly with the Recordset 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. Transfer parameters to the stored procedure
If you do not need parameters during the stored procedure, but a single SQL statement, it also displays the advantages of no adjustment stored procedures!
For example, a BBS query can be inquired by the author and theme! The stored procedure can be established as follows:
The parameter keyword is keyword, and Choose is a way 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
This way we call the stored procedure, just pass the parameters, and save 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, Adpaaminput, 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 = 3rs.open comm, 1, 1
3. Further discussion
Compare the two methods I have said in the ASP species, the memory process,
The first method requires less object, but the property supports the Recordset object has a lot of properties, such as rs.recordcount, rs.pagecount, rs.absolutepage these properties
Do not support, so use the first method, there is a limitation, for example, when the record is displayed, the second method must be used.
We use the stored procedures in SQL Server to speed up the speed, but there are many SQL statements during a stored procedure, its advantages are particularly obvious. If the SQL statement is not a lot,
And we must create a Command object with the second method, which may also slow down! So we have to balance the interests of all aspects to use the stored procedure.
However, I think how it is, using the stored procedure, make the program more modular, easy to modify, and debug (you can debug directly under SQL Server without having to look at ASP under IE
the result of).
