Learning to use the stored procedure, it is one of the ASP programmers. All large databases support stored procedures, such as Oracle, MS SQL, etc. (but MS Access is not supported, however, parameterized queries can be used in Access). 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. 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), the internal function stored procedure writing
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.
Create Procedure ORDER_TOT_AMT @o_id int, @p_tot = sum (unitprice * quantity) from orderdetails where order = @ o_id
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 has many benefits to calling this stored procedure by @P_tot This parameter output by @P_tot has many benefits, which can encapsulate complex data logic and give full play to the advantage of large database itself. We know that ASP is not suitable for complex data operations, and accesses the database through the OLD DB, because the data needs to be passed between ASP and databases, quite consumption system resources. In fact, if the database only plays a role of data storage, then its function is far from being utilized. For how to create a stored procedure, please refer to the relevant documentation of MS SQL. This article describes how the stored procedure is used in ASP. Simple SQL statement:
Select ID, Name, Picture, Time, Duty from Employ
We can create a stored procedure:
Create Procedure SP_EMPLOYASSELECT ID, NAME, PICTURE, TIME, DUTY from Employ Go, SQL statement:
Select ID, Name, Picture, Time, Duty from Employ Where ID = 10230
The corresponding stored procedure is: (replace our existing stored procedures with alter)
Alter Procedure Sp_employ @ InID INTASSELECT ID, NAME, PICTURE, TIME, DUTY from Employ Where Id = @ inIdgo
The case where SQL and stored procedures are in ASP below. First see if SQL is executed directly:
<% DIM CONN, STRSQL, RSSET 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)%>
How to perform look Stored Procedure: <% dim Conn, strSQL, rsset Conn = Server.CreateObject ( "ADODB.Connection") Conn.Open "DSN = webData; uid = user; pwd = password" 'make connectionstrSQL = "sp_employ "SET RS = conn.execute (strsql)%> Stored Procedure with parameters is also quite similar:
<% Dim Conn, strSQL, rs, myIntmyInt = 1 set Conn = Server.CreateObject ( "ADODB.Connection") Conn.Open "DSN = webData; uid = user; pwd = password" strSQL = "sp_myStoredProcedure" & myIntSet rs = Conn.execute (strsql)%>
Perform a stored procedure in SQL Server In the SQL Server Query Analyzer, enter the following code: declare @tot_amt intexecute order_tot_amt 1, @ TOT_AMT OUTSELECT @tot_AMT OutputSelect @tot_AMT OutputSelect @tot_AMT OutputSelect @tot_amt OutputSelect @tot_amt OutputSelect @tot_AMT OutputSelect @tot_AMT The above code is the stored procedure to execute the order_tot_amt to calculate the order number 1 Order sales amount, we define @tot_amt as output parameters, used to undertake the results we want to call stored procedures in ASP
