Stored procedure 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"
Set Rs=server.createobject ("Adodb.recordset")
Rs. Cursortype=3
Rs.Open comm,,1,1
3. Further discussion
Compare the two methods I mentioned above to invoke the stored procedure in ASP.
The first method requires fewer objects, but the Recordset object supports a much smaller number of attributes, such as: rs.recordcount,rs.pagecount,rs.absolutepage these attributes
are not supported, so there are limitations in the first method, for example, when you want to page the record, you have to use the second method.
We use the stored procedures in SQL Server to speed up, but the advantages of a stored procedure when there are many SQL statements are particularly obvious. If the SQL statements are not many,
And we have to create one more command object with the second method, and it may slow down! So we have to balance the interests of all parties to use stored procedures.
But I don't think so. Using stored procedures can make programs more modular, easy to modify, and debug (you can debug directly in SQL Server without looking at ASP under IE)
The results).
