Stored procedures | Stored procedures learning to use stored procedures (Stored Procedure) is one of the necessary lessons for an ASP programmer. All large databases support stored procedures, such as Oracle, MS SQL, and so on (but not supported by MS Access, although parameterized queries can be used in access).
There are many benefits to using stored procedures, which can encapsulate complex data logic and give full play to the advantages of the large database itself. We know that ASP is not suitable for complex data operations, and through the old DB access to the database, because the data needs to be passed between the ASP and the database, considerable consumption of system resources. In fact, if the database only acts as a data store, its functionality is far from being exploited.
Refer to the documentation for MS SQL for information on how to create a stored procedure.
This article describes how stored procedures are used in ASP.
A simple SQL statement:
Select Id,name,picture,time,duty from employ
We can create a stored procedure:
CREATE PROCEDURE Sp_employ
As
Select Id,name,picture,time,duty from employ
Go
and the SQL statement:
Select Id,name,picture,time,duty from employ where id=10230
The corresponding stored procedure is: (Replaces our existing stored procedure with alter)
ALTER PROCEDURE Sp_employ
@inID int
As
Select Id,name,picture,time,duty from employ where id= @inID
Go
The following is a comparison of the SQL and stored procedures in the ASP. First look at the direct execution of SQL:
<%
Dim Conn, strSQL, RS
Set Conn = Server.CreateObject ("ADODB. Connection ")
Conn.Open "Dsn=webdata;uid=user;pwd=password"
strSQL = "Select Id,name,picture,time,duty from Employ"
Set rs = Conn.execute (strSQL)
%>
and see how to perform stored Procedure:
<%
Dim Conn, strSQL, RS
Set Conn = Server.CreateObject ("ADODB. Connection ")
Conn.Open "Dsn=webdata;uid=user;pwd=password" ' Make connection
strSQL = "Sp_employ"
Set rs = Conn.execute (strSQL)
%>
The stored procedure that performs the parameter is also quite similar:
<%
Dim Conn, strSQL, RS, myInt
MyInt = 1
Set Conn = Server.CreateObject ("ADODB. Connection ")
Conn.Open "Dsn=webdata;uid=user;pwd=password"
strSQL = "Sp_mystoredprocedure" & MyInt
Set rs = Conn.execute (strSQL)
%>
You may find it easy to use stored procedures in an ASP. Right! It's so simple.
Turn from: http://goaler.xicp.net/ShowLog.asp?ID=503
