ASP invokes a stored procedure to access SQL Server

server| Stored Procedures | There are many articles accessing ASP and stored procedures (Stored procedures), but I doubt whether the authors have actually practiced them. I have consulted a lot of relevant data at the beginning of the class, and found that many of the methods provided are not the case. For simple applications, this information may be helpful, but it is limited to this, because they are simply stereotyped, mutual plagiarism, a slightly more complex application, all vague.
Now, I basically access SQL Server by calling stored procedures, the following text is not guaranteed to be absolutely correct, but it is a summary of the practice, I hope to be helpful to everyone.

A stored procedure is one or more SQL commands that are stored as executable objects in the database.
Definitions are always abstract. A stored procedure is actually a set of SQL statements that can do something, except that the set of statements is in the database (here we talk about SQL Server). If we create stored procedures and call stored procedures in ASP, we can avoid mixing SQL statements with ASP code. There are at least three benefits to doing this:
First, greatly improve efficiency. The stored procedure itself executes very quickly, and invoking a stored procedure can significantly reduce the number of interactions with the database.
Second, improve security. If the SQL statement mixed in the ASP code, once the code compromised, but also means that the library structure compromised.
Third, to facilitate the reuse of SQL statements.

In ASP, the stored procedure is usually invoked through the command object, and the other invocation methods are described in this article according to different situations. To facilitate the description, according to the input and output of the stored procedure, make the following simple categories:
1. A stored procedure that returns only a single recordset
The following stored procedures are assumed (the purpose of this article is not to tell the T-SQL syntax, so the stored procedure gives the code only, not the description):

/*sp1*/
CREATE PROCEDURE Dbo.getuserlist
As
SET NOCOUNT ON
Begin
SELECT * FROM dbo. [UserInfo]
End
Go

The above stored procedure gets all the records in the UserInfo table and returns a recordset. The ASP code that invokes the stored procedure through the Command object is as follows:

' * * Call stored procedure via command object * *
DIM Mycomm,myrst
Set Mycomm = Server.CreateObject ("Adodb.command")
mycomm.activeconnection = Myconstr ' myconstr is a database connection string
Mycomm.commandtext = "Getuserlist" ' specifies stored procedure name
Mycomm.commandtype = 4 ' indicates that this is a stored procedure
Mycomm.prepared = True ' requires that SQL commands be compiled in advance
Set Myrst = Mycomm.execute
Set Mycomm = Nothing

    the recordset obtained by the stored procedure is assigned to Myrst, and then the Myrst can be manipulated.
    in the above code, the CommandType property indicates the type of the request, and the value and description is as follows:
      -1   Indicates that the type of the CommandText parameter cannot be determined
      1    indicates that CommandText is a generic command type
       2    indicates that the CommandText parameter is an existing table name
      4     indicates that the CommandText parameter is the name of a stored procedure
   
    You can also invoke stored procedures by connection objects or Recordset objects, respectively, by using the following methods:

' * * Invoke stored procedure via connection object * *
DIM Myconn,myrst
Set myconn = Server.CreateObject ("ADODB. Connection ")
MyConn.Open Myconstr ' myconstr is a database connection string
Set Myrst = Myconn.execute ("Getuserlist", 0,4) ' last argument meaning same as CommandType
Set myconn = Nothing

' * * Call stored procedure via Recordset object * *
DIM Myrst
Set Myrst = Server.CreateObject ("ADODB. Recordset ")
Myrst.open "Getuserlist", myconstr,0,1,4
' Myconstr is a database connection string, and the last argument has the same meaning as CommandType

2. Stored procedures without input and output
Please see the following stored procedures:

/*sp2*/
CREATE PROCEDURE Dbo.deluserall
As
SET NOCOUNT ON
Begin
Delete FROM dbo. [UserInfo]
End
Go

The stored procedure deletes all records in the UserInfo table, without any input or output, and the invocation method is essentially the same as the above, except that the recordset is not obtained:

' * * Call stored procedure via command object * *
DIM Mycomm
Set Mycomm = Server.CreateObject ("Adodb.command")
mycomm.activeconnection = Myconstr ' myconstr is a database connection string
Mycomm.commandtext = "Deluserall" ' specifies stored procedure name
Mycomm.commandtype = 4 ' indicates that this is a stored procedure
Mycomm.prepared = True ' requires that SQL commands be compiled in advance
Mycomm.execute &nbs