Introduction to stored procedures and use of ASP stored procedures

Stored procedures First, introduce what is stored procedure


stored procedures are programs that are 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 kind of language mainly provides the following function, lets the user design conforms to the reference request the procedure:

1), Variable description

2), ANSI-compatible SQL commands (such as select,update ...)

3), General Process Control commands (If...else ..., while ...)
4), internal function
second, writing lattice of stored procedure

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. Set up to 1024 parameters per stored procedure

(version above SQL Server 7.0), the parameters are used as follows:

@ 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, which is supported by other SQL Server 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.
Example:
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
Example Description:
This example is to establish a simple stored procedure Order_tot_amt, which calculates the total sales [unit Price (UnitPrice) * Quantity of the order according to the Order ID number (@o_id) entered by the user (OrderDetails) ( Quantity)], this amount is output by @p_tot This parameter to the program that invokes the stored procedure
three, execute 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 the execution order_tot_amt This stored procedure, calculates the order number 1 the order sale amount, we define @tot_amt as the output parameter, is used to undertake the result which we want
Four, call a stored procedure in ASP
<!--must load adovbs.inc files, otherwise an error will occur-->
<!--#include file= "Adovbs.inc"-->
<%
Dim objcnn
Dim objcmd
Dim Rs
Const O_ID=112
'-----Establish a Connection object----------
set Objcnn=server.createobject ("Adodb.connection")
Objcnn.open "Driver={sql server};server=localhost;uid=sa;pwd=cncanet;database=check;"
'-----Establish 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 stored procedure parameters-------
ObjCmd.Parameters.Append _
objcmd.createparameter ("o_id", adinteger,adparaminput,,o_id)
ObjCmd.Parameters.Append _
Objcmd.createparameter ("P_tot", adbigint,adparamoutput,,0)

'-----Execute stored procedures----------------------
Objcmd.execute
'-----output parameters and processing results--------------
for each parm in Objcmd.parameters
Response.Write Parm.name & "=" & Trim (parm) & "<br>"
Next
%>