First, let's introduce what is a stored procedure.
The stored procedure is a program written by using the TRANACT-SQL language provided by SQL Server. The TRANACT-SQL language is SQL Server provides a language designed to design a database application, which is the primary programming interface between the application and the SQL Server database. It is better than the Informix-4GL language that Pro-SQL and Informix in the Oracle Database system is in the INFORMIX-4GL language. Such languages mainly provide the following functions, allowing users to design programs that meet the reference requirements:
1), variable description
2), ANSI compatible SQL commands (such as SELECT, UPDATE ....)
3) General flow control command (if ... else ..., while ....)
4), internal functions
Second, the writing of the stored procedure
CREATE Procedure [owner.] Store process name [; program number]
[(Parameter # 1, ... parameter # 1024)]]
[With
{Recompile | Encryption | Recompile, Encryption}
]
[For replication]
AS line
The stored procedure name cannot exceed 128 words. Set up to 1024 parameters per stored procedure
(SQL Server 7.0 or higher), the method of use of parameters is as follows:
@ 参数 Name Data Type [Varying] [= Normal] [OUTPUT]
There is a "@" symbol before each parameter name. The parameters of each stored procedure are only internal to the program, and the type of data supported by other SQL Server is available in addition to Image.
[= Obredity] It is equivalent to setting the default value of a field when we set up a database, here is the default value for this parameter. [OUTPUT] is used to specify that the parameter is both input and output values, that is, when the stored procedure is called, if the specified parameter value is the parameters we need to enter, it also needs to be output in the results. If the item must be OUTPUT, if only the output parameter is used, you can use Cursor, and when using this parameter, you must specify the two statements of Varying and Output.
example:
CREATE Procedure Order_tot_amt @o_id int, @ p_tot int output as
SELECT @P_tot = SUM (Unitprice * Quantity)
From OrderDetails
Where orderdered = @ o_id
Example description:
This example is a simple stored procedure order_tot_amt, which calculates the total sum sales according to the order ID number (@o_id) entered by the user, and the order of the unit (unitprice) * quantity] This amount outputs a program that calls this stored procedure by @P_tot.
Third, perform a stored procedure in SQL Server
In the SQL Server query analyzer, enter the following code:
Declare @tot_amt int int
EXECUTE ORDER_TOT_AMT 1, @ Tot_AMT Output
SELECT @tot_amt
The above code is the stored procedure for executing the order_tot_amt to calculate the order sales amount of 1, we define @tot_amt as the output parameters, used to undertake the results we want
Fourth, call the stored process in the ASP
<%
DIM OBJCNN
DIM OBJCMD
DIM RS
Const o_id = 112
'----- Create a Connection object ----------
Set objcnn = server.createObject ("adoDb.connection") objcn.open "driver = {SQL Server}; server = localhost; uid = sa; pwd = cncanet; Database = Check;"
'----- Create a Command Object -----------
Set objcmd = server.createObject ("adoDb.command")
Objcmd.activeConnection = Objcnn
Objcmd.commandtext = "ORDER_TOT_AMT" "Specifies the stored procedure name
Objcmd.commandtype = adcmdstoredProc 'It is Stored Procedure
'----- Prepare the parameters of Stored Procedure -------
Objcmd.parameters.Append_
Objcmd.createParameter ("O_ID", Adinteger, Adparaminput, O_ID)
Objcmd.parameters.Append_
Objcmd.createParameter ("p_tot", adbigint, adpaamoutput ,, 0)
'----- Execute stored procedures ----------------------
Objcmd.execute
'----- Output parameters and processing results --------------
For Each Parm in Objcmd.Parameters
Response.write parm.name & "=" & trim (parm) & "
"
NEXT
%>
