Learn to use stored procedures in ASP

Learning to use stored procedures is a must for ASP programmers. All large databases support stored procedures, such as Oracle and ms SQL. (However, MS Access is not supported. However, parameterized queries can be used in access ).
Stored Procedures have many advantages. They 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, but accessing the database through the old dB, because the data needs to be transmitted between ASP and the database, it consumes a considerable amount of system resources. In fact, if a database only plays a role in data storage, its functions are far from being used.
For more information about how to create a stored procedure, see the ms SQL documentation.
This article describes how to use stored procedures 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
 

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
 

The following compares SQL statements 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 take a look at how to execute 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 execution of 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 simple. Yes! That's simple.

From: http://goaler.xicp.net/ShowLog.asp? Id = 503