Using stored procedures in ASP

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).

A stored procedure is a program that is written using the Tranact-sql language provided by SQL Server. The Tranact-sql language is the language that SQL Server provides specifically for designing database applications, which is the primary programming interface between applications and SQL Server databases. It is like the Pro-sql in an Oracle database system and the INFORMIX-4GL language available in the database system of Informix. This type of language provides the following features, allowing users to design a program that meets the needs of the reference:
1), Variable description
2), ANSI-compatible SQL commands (such as select,update ...)
3), General Process Control command (If...else ..., while ...)
4), internal function

Writing grid for Stored procedures


CREATE PROCEDURE [owner.] stored procedure name [; program number]
[Parameter # #,... Parameter #1024)]
[With
{RECOMPILE | Encryption | RECOMPILE, encryption}
]
[For REPLICATION]
As Program line



The stored procedure name cannot be more than 128 words. A maximum of 1024 parameters per stored procedure (SQL Server 7.0 version above) is used for the following parameters:


@ Parameter name data type [varying] [= default] [OUTPUT]



Each parameter name must have an "@" symbol, and the parameters for each stored procedure are used internally by the program only, and the type of the parameter can be used in addition to image for other SQL Server-supported data types.

[= default] is the same as when we set up the database to set a field defaults, here is to set the default value for this parameter. [Output] is used to specify that the parameter is both an input and an output value. That is, when the stored procedure is invoked, if the specified parameter value is the parameter we need to enter, and also needs to be output in the result, the item must be in output, and if it is only for output parameters, you can use the cursor , and you must specify both the varying and output statements when using this parameter.


CREATE PROCEDURE order_tot_amt @o_id int, @p_tot int output as
SELECT @p_tot = SUM (unitprice*quantity)
From OrderDetails
WHERE ordered= @o_id




The example is to establish a simple stored procedure Order_tot_amt, which calculates the total sales of the order (OrderDetails) according to the Order ID number (@o_id) entered by the user, [unit Price (UnitPrice) * Quantity ( Quantity)], this amount is output by @p_tot This parameter to the program that invokes the stored procedure

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)
%>




Executing stored procedures in SQL Server

In SQL Server Query Analyzer, enter the following code:
DECLARE @tot_amt int
Execute Order_tot_amt 1, @tot_amt output
Select @tot_amt

The above code is to execute order_tot_amt this stored procedure, to calculate the order number 1 of the order sales amount, we define @tot_amt as the output parameter, to accept the result we want


Calling a stored procedure in ASP


<!--must load the Adovbs.inc file, otherwise there will be an error-->
<!--#include file= "Adovbs.inc"-->
<%
Dim objcnn
Dim objcmd
Dim Rs
Const O_ID=112

'-----Set up the Connection object----------
Set Objcnn=server.createobject ("Adodb.connection")
Objcnn.open "Driver={sql Server};server=localhost;uid=sa;pwd=cncanet;database=check;"
'-----Establish the Command object-----------
Set Objcmd=server.createobject ("Adodb.command")
Objcmd.activeconnection=objcnn
objcmd.commandtext= "Order_tot_amt" ' specifies the name of the stored procedure
Objcmd.commandtype=adcmdstoredproc ' It is stored Procedure
'-----Prepare the parameters for stored procedure-------
ObjCmd.Parameters.Append _
Objcmd.createparameter ("o_id", adinteger,adparaminput,,o_id)
ObjCmd.Parameters.Append _
Objcmd.createparameter ("P_tot", adbigint,adparamoutput,,0)
'-----Execute the stored procedure----------------------
Objcmd.execute

'-----output parameters and processing results--------------
For each parm in Objcmd.parameters
Response.Write Parm.name &a