ASP calls Stored Procedure

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 the stored procedure does not use parameters, but is a single SQL statement, it does not show the advantages of calling the stored procedure!
For example, you can query a BBS by author or topic! You can create a stored procedure as follows:
The keyword parameter is a keyword, And the choose parameter is the query method.
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, we only need to pass the parameters when calling the stored procedure, instead of writing a section in ASP. Program
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
Compare the two methods I mentioned above to call stored procedures in ASP,
The first method requires fewer objects, but the recordset object supports fewer attributes, such as Rs. recordcount, Rs. pagecount, Rs. absolutepage.
Not supported, so the first method is limited. For example, to display records by page, the second method must be used.
We use the stored procedures in SQL Server 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,
In the second method, we must create one more command object, 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 satisfactory. Using the stored procedure can make the program more modular, easy to modify, and debug (it can be directly debugged under SQL Server, instead of viewing ASP results in IE ).