Getting started with ASP Stored Procedure Writing Page 1/5

ASP and stored procedure (stored procedures) Article Many, but I doubt whether the authors have actually practiced it. I checked a large amount of relevant materials at the beginning and found that many of the methods provided in them were not the same in practice. For simple applications, these materials may be helpful, but they are limited to this, because they are all the same, copy each other, and use a little more complex.

Now, I basically access SQL Server by calling the stored procedure. The following words are a summary of the practices and hope to help you.

A stored procedure is one or more SQL commands stored as executable objects in the database.

Definition is always abstract. The stored procedure is actually a set of SQL statements that can complete certain operations, but these statements are stored in the database (Here we only talk about SQL Server ). If we create a stored procedure and call the stored procedure in ASP, we can avoidCode. There are at least three advantages:

First, greatly improve efficiency. Stored procedures are executed very quickly, and calling stored procedures can greatly reduce the number of interactions with the database.
Second, improve security. If SQL statements are mixed in ASP code, once the code is out of password, it also means that the database structure is out of password.
Third, it is conducive to the reuse of SQL statements.

In ASP, the stored procedure is generally called through the command object. Based on different situations, this article also introduces other calling methods. For convenience, the following simple classification is made based on the input and output of the stored procedure:
1. Only the stored procedure of a single record set is returned.
Suppose there are the following stored procedures (the purpose of this article is not to describe the T-SQL syntax, so the stored procedure only gives code, not to mention ):
/* SP1 */Copy codeThe Code is as follows: Create procedure DBO. getuserlist
As
Set nocount on
Begin
Select * From DBO. [userinfo]
End
Go

The above Stored Procedure Retrieves all records in the userinfo table and returns a record set. The ASP code for calling the stored procedure through the command object is as follows:
'** Call the stored procedure through the command object **
Dim mycomm, myrst
Set mycomm = server. Createobject ("ADODB. Command ")
Mycomm. activeconnection = myconstr 'myconstr is the database connection string
Mycomm. commandtext = "getuserlist" 'specifies the name of the stored procedure.
Mycomm. commandtype = 4 'indicates that this is a stored procedure
Mycomm. Prepared = true 'requires that the SQL command be compiled first
Set myrst = mycomm. Execute
Set mycomm = nothing
The set of records obtained by the stored procedure is assigned to myrst. Next, you can operate on myrst.

In the preceding code, the commandtype attribute indicates the request type. The values and descriptions are as follows:
-1 indicates that the commandtext parameter type cannot be determined
1 indicates that commandtext is a common command type
2. The commandtext parameter is an existing table name.
4. The commandtext parameter is the name of a stored procedure.
You can also call the stored procedure through the connection object or recordset object. The methods are as follows:Copy codeThe Code is as follows: '** call the stored procedure through the connection object **
Dim myconn, myrst
Set myconn = server. Createobject ("ADODB. Connection ")
Myconn. Open myconstr 'myconstr is the database connection string
Set myrst = myconn. Execute ("getuserlist",) 'the last parameter is the same as commandtype
Set myconn = nothingCopy codeThe Code is as follows: '** call the stored procedure through the recordset object **
Dim myrst
Set myrst = server. Createobject ("ADODB. recordset ")
Myrst. Open "getuserlist", myconstr, 0, 1, 4
'Myconstr is the database connection string. The last parameter is of the same meaning as commandtype.