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 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
Writing of stored procedures
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 int output as
SELECT @P_tot = SUM (Unitprice * Quantity)
From OrderDetails
Where orderdered = @ 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 outputs a program that calls this stored procedure by @P_tot.
There are many benefits to using the stored procedure, which can encapsulate complex data logic and give full play to the advantages 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_EMPLOY
AS
Select ID, Name, Picture, Time, Duty from Employ
Go
And 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 Int
AS
Select ID, Name, Picture, Time, Duty from Employ Where Id = @ inID
Go
The case where SQL and stored procedures are in ASP below. First see if SQL is executed directly:
<%
DIM CONN, STRSQL, RS
Set 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)
%>
Let's take a look at how to perform Stored Procedure:
<%
DIM CONN, STRSQL, RS
Set conn = server.createObject ("adoDb.connection")
Conn.open "DSN = WebData; UID = USER; PWD = Password" 'make Connection
strsql = "sp_employ"
SET RS = conn.execute (strsql)
%>
Stored Procedure with parameters is also quite similar:
<%
DIM CONN, STRSQL, RS, Myint
Myint = 1
Set conn = server.createObject ("adoDb.connection")
Conn.open "DSN = WebData; UID = USER; PWD = Password"
strsql = "sp_mystoredprocedure" & myint
SET RS = conn.execute (strsql)
%>
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
Adjustment of stored procedures in 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
%>
