Full-touch application of stored procedures in ASP development

ASP and stored procedures (Stored procedures) article a lot, but I doubt whether the authors really practice. 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 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:

'**通过Command对象调用存储过程**
DIM MyComm,MyRst
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr 'MyConStr是数据库连接字串
MyComm.CommandText = "getUserList" '指定存储过程名
MyComm.CommandType = 4 '表明这是一个存储过程
MyComm.Prepared = true '要求将SQL命令先行编译
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 are 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 a table name that exists

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:

'**通过Connection对象调用存储过程**
DIM MyConn,MyRst
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.open MyConStr 'MyConStr是数据库连接字串
Set MyRst = MyConn.Execute("getUserList",0,4) '最后一个参断含义同CommandType
Set MyConn = Nothing
'**通过Recordset对象调用存储过程**
DIM MyRst
Set MyRst = Server.CreateObject("ADODB.Recordset")
MyRst.open "getUserList",MyConStr,0,1,4
'MyConStr是数据库连接字串,最后一个参断含义与CommandType相同