Learning to use stored procedures (Stored Procedure) is one of the necessary courses for ASP programmers. All large databases support stored procedures, such as Oracle, MS SQL, etc. (but MS Access does not support it. However, parameterized queries can be used in Access).
There are many benefits to using stored procedures. It can encapsulate complex data logic and give full play to the advantages of large databases. We know that ASP is not suitable for complex data operations, and accessing the database through OLD DB consumes system resources because data needs to be transferred between ASP and the database. In fact, if the database only plays the role of data storage, then its functions are far from being utilized.
For information on how to create a stored procedure, please refer to the relevant MS SQL documentation.
This article introduces 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: (replace 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
Let's compare the situation of SQL and stored procedures in ASP. First, let’s 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)
%>
Let’s look at how to execute the 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)
%>
Executing a Stored Procedure with parameters is 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 think that using stored procedures in ASP is so simple. right! It's that simple.