There are many articles on ASP and Stored Procedures, but I doubt that the authors have actually practiced it. I read a lot of relevant information when I was in the beginning and found that many of the methods provided were not the case in practice. For simple applications, these materials may be helpful, but only because they are simply the same, plagiarizing each other, and slightly more complex applications will be vague.
Now, I basically access SQLServer by calling stored procedures. The following text is a summary of practice, and I hope it will be helpful to everyone.
A stored procedure is one or more SQL commands stored in the database as executable objects.
Definition is always abstract. Stored procedures are actually a set of SQL statements that can complete certain operations, but this set of statements is placed in the database (we will only talk about SQLServer here). 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 of doing this:
First, greatly improve efficiency. The stored procedure itself is very fast, and calling stored procedure can greatly reduce the number of interactions with the database.
Second, improve safety. If SQL statements are mixed in ASP code, once the code is lost, it means that the library structure is lost.
Third, it is conducive to the reuse of SQL statements.
In ASP, stored procedures are generally called through command objects. According to different situations, this article also introduces other calling methods. For convenience of explanation, the following simple classification is made based on the input and output of the stored procedure:
1. Only return stored procedures for a single record set
Suppose there is the following stored procedure (the purpose of this article is not to describe the T-SQL syntax, so the stored procedure only gives code and does not explain):
/*SP1*/
The code copy is as follows:
CREATEPROCEDUREdbo.getUserList
as
setnocounton
Begin
select*fromdbo.[userinfo]
end
go
The above stored procedure obtains 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:
'**Calling procedure through Command object**
DIMMyComm,MyRst
SetMyComm=Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection=MyConStr'MyConStr is the database connection string
MyComm.CommandText="getUserList"'Specify the stored procedure name
MyComm.CommandType=4' indicates that this is a stored procedure
MyComm.Prepared=true' requires SQL commands to be compiled first
SetMyRst=MyComm.Execute
SetMyComm=Nothing
The record set obtained by the stored procedure is assigned to MyRst. Next, MyRst can be operated.
In the above code, the CommandType property indicates the type of the request, the value and description are as follows: