First, introduce what is that the stored procedure stored procedure is the program written by 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. Such languages provide the following functions, allowing users to design programs that meet the reference requirements: 1), variables 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 during each store (SQL Server 7.0 or higher), the method of use of the 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 = sum (unitprice * quantity) from OrderDetails Where Ordered = @ o_id
Example: This example is a simple stored procedure order_tot_amt, which calculates the total sales of the order (Unitprice *) according to the order ID number (@o_id) entered by the user input, and the unit price (Unitprice *) Quantity)] This amount outputs a program that calls this stored procedure by @P_tot this parameter.
Third, perform a stored procedure in SQL Server
In the Query Analyzer of SQL Server, enter the following code: declare @tot_amt 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 objcn = 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 = Objcn Objcmd. CommandText = "order_tot_amt" 'specifies the name of the stored procedure objCmd.CommandType = adCmdStoredProc' as a parameter stored procedure '----- stored procedure to prepare ------- 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%>
