Introduction to stored procedures and use of asp stored procedures

I. first introduce what is a stored procedure
Stored procedures are programs written using the Tranact-SQL language provided by SQL Server. Tranact-SQL is a language provided by SQL Server for designing database applications. It is the main programming interface between applications and SQL Server databases. It is like the Informix-4GL language in the Oracle database system's Pro-SQL and Informix database system. This type of language mainly provides the following functions, allowing users to design a program that meets the reference requirements:
1) variable description
2) ANSI compatible SQL commands (such as Select, Update ....)
3) general process control commands (if... Else... , While ....)
4) internal functions

Ii. Writing format of the stored procedure

Create procedure [owner.] stored PROCEDURE name [; program id]
[(Parameter #1 ,... Parameter #1024)]
[
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
]
[For replication]
AS program line

The name of the stored procedure cannot exceed 128 characters. A maximum of 1024 parameters can be set in each stored procedure.
(SQL Server 7.0 or later), the parameter usage is as follows:

@ Parameter Name Data Type [VARYING] [= internal value] [OUTPUT]

Each parameter name must have a "@" symbol before it. The parameters of each stored procedure are only used internally by the program. The parameter types include IMAGE, data Types supported by other SQL servers can be used.
[= Internal value] is equivalent to setting the default value of a field when creating a database. Here we set the default value for this parameter. [OUTPUT] is used to specify that this parameter has both input and OUTPUT values. That is, when the stored procedure is called, if the specified parameter value is the parameter we need to enter, if the parameter must be OUTPUT in the result, it must be OUTPUT. If the parameter is used only for OUTPUT, you can use CURSOR, the VARYING and OUTPUT statements must be specified.

Example:
Create procedure order_tot_amt @ o_id int, @ p_tot int output
SELECT @ p_tot = sum (Unitprice * Quantity)
FROM orderdetails
WHERE ordered = @ o_id

Example:
In this example, a simple Stored Procedure order_tot_amt is created. This stored procedure is based on the order ID number (@ o_id) entered by the user, by the Order List (orderdetails) calculates the total sales of this Order [unit price * Quantity (Quantity)]. This amount is output to the program that calls this stored procedure through @ p_tot.

3. Execute the stored procedure in SQL Server

In the 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 executes the order_tot_amt stored procedure and calculates the order sales amount with Order Number 1. We define @ tot_amt as the output parameter to undertake the results we want.

4. Calling stored procedures in ASP

<! -- The adovbs. inc file must be loaded; otherwise, an error occurs. -->
<! -- # Include file = "adovbs. inc" -->
<%
Dim objCnn
Dim objCmd
Dim Rs
Const o_id = 112

'----- Create a Connection object ----------
Set objCnn = Server. CreateObject ("Adodb. connection ")
ObjCnn. Open "driver = {SQL server}; server = localhost; uid = sa; pwd = cncanet; database = check ;"
'----- CREATE 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 = adw.storedproc ': Stored Procedure
'----- Prepare the stored procedure Parameter -------
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 & "=" & trim (parm) & "<br>"
Next
%>