A stored process is one or more SQL commands stored in the database as executable objects.
Definitions are always abstract. A stored process is actually a set of SQL statements that can complete certain operations, but this set of statements is placed in the database (here we only talk about SQL SERVER). If we create a stored process and call the stored process in ASP, we can avoid mixing SQL statements with ASP code. There are at least three benefits to doing this:
First, greatly improve efficiency. The execution speed of the stored process itself is very fast, and calling the stored process can greatly reduce the number of interactions with the database.
Second, improve safety. If you mix SQL statements in ASP code, once the code is compromised, it also means that the library structure is compromised.
Third, it is conducive to the reuse of SQL statements.
In ASP, stored processes are generally called through the COMMAND object. Depending on the situation, this article also introduces other calling methods. For the convenience of explanation, the following simple classifications are made based on the input and output of the stored process:
1. A stored process that only returns a single record set
Suppose there is the following stored process (the purpose of this article is not to describe T-SQL syntax, so the stored process only gives the code without explanation):
/*SP1*/
CREATE PROCEDURE DBO.GETUSERLIST
AS
SET NOCOUNT ON
BEGIN
SELECT * FROM DBO.[USERINFO]
END
GO
The above stored process obtains all records in the USERINFO table and returns a record set. The ASP code for calling the stored process through the COMMAND object is as follows:
'**Call stored process through COMMAND object**
DIM MYCOMM,MYRST
SET MYCOMM = SERVER.CREATEOBJECT(ADODB.COMMAND)
MYCOMM.ACTIVECONNECTION = MYCONSTR 'MYCONSTR is the database connection string
MYCOMM.COMMANDTEXT = GETUSERLIST 'Specify the stored process name
MYCOMM.COMMANDTYPE = 4 'Indicates that this is a stored process
MYCOMM.PREPARED = TRUE 'Require SQL commands to be compiled first
SET MYRST = MYCOMM.EXECUTE
SET MYCOMM = NOTHING
The record set obtained by the storage process is assigned to MYRST. Next, MYRST can be operated.
In the above code, the COMMANDTYPE attribute indicates the type of request. The value and description are as follows:
-1 indicates that the type of the COMMANDTEXT parameter cannot be determined
1 indicates that COMMANDTEXT is a general command type
2 indicates that the COMMANDTEXT parameter is a table name with
4 indicates that the COMMANDTEXT parameter is the name of a stored process
You can also call the stored process through the CONNECTION object or RECORDSET object. The methods are as follows:
'**Calling the stored process through the CONNECTION object**
DIMMYCONN,MYRST
SET MYCONN = SERVER.CREATEOBJECT(ADODB.CONNECTION)
MYCONN.OPEN MYCONSTR 'MYCONSTR is the database connection string
SET MYRST = MYCONN.EXECUTE(GETUSERLIST,0,4) 'The last parameter has the same meaning as COMMANDTYPE
SET MYCONN = NOTHING
'**Calling the stored process through the RECORDSET object**
DIMMYRST
SET MYRST = SERVER.CREATEOBJECT(ADODB.RECORDSET)
MYRST.OPEN GETUSERLIST,MYCONSTR,0,1,4
'MYCONSTR is the database connection string, the last parameter has the same meaning as COMMANDTYPE
2. Stored process with no input and output
Take a look at the following stored procedures:
/*SP2*/
CREATE PROCEDURE DBO.DELUSERALL
AS
SET NOCOUNT ON
BEGIN
DELETE FROM DBO.[USERINFO]
END
GO
This stored process deletes all records in the USERINFO table without any input or output. The calling method is basically the same as mentioned above, except that there is no need to obtain the record set:
'**Call stored process through COMMAND object**
DIMMYCOMM
SET MYCOMM = SERVER.CREATEOBJECT(ADODB.COMMAND)
MYCOMM.ACTIVECONNECTION = MYCONSTR 'MYCONSTR is the database connection string
MYCOMM.COMMANDTEXT = DELUSERALL 'Specify the stored process name
MYCOMM.COMMANDTYPE = 4 'Indicates that this is a stored process
MYCOMM.PREPARED = TRUE 'Require SQL commands to be compiled first
MYCOMM.EXECUTE 'No need to obtain the record set here
SET MYCOMM = NOTHING
Of course, this type of stored process can also be called through the CONNECTION object or RECORDSET object. However, the RECORDSET object is created to obtain the record set. If the record set is not returned, use the COMMAND object.
3. Stored procedures with return values
When performing SP2-like operations, you should make full use of the powerful transaction processing capabilities of SQL SERVER to maintain data consistency. Moreover, we may need to store the execution status returned by the process. To this end, modify SP2 as follows:
/*SP3*/
CREATE PROCEDURE DBO.DELUSERALL
AS
SET NOCOUNT ON
BEGIN
BEGIN TRANSACTION
DELETE FROM DBO.[USERINFO]
IF @@ERROR=0
BEGIN
COMMIT TRANSACTION
RETURN 1
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN 0
END
RETURN
END
GO
The above stored process returns 1 when DELETE is executed successfully, otherwise it returns 0 and performs rollback operation. In order to obtain the return value in ASP, you need to use the PARAMETERS collection to declare parameters:
'**Call a stored process with a return value and get the return value**
DIM MYCOMM,MYPARA
SET MYCOMM = SERVER.CREATEOBJECT(ADODB.COMMAND)
MYCOMM.ACTIVECONNECTION = MYCONSTR 'MYCONSTR is the database connection string
MYCOMM.COMMANDTEXT = DELUSERALL 'Specify the stored process name
MYCOMM.COMMANDTYPE = 4 'Indicates that this is a stored process
MYCOMM.PREPARED = TRUE 'Require SQL commands to be compiled first
'Declare return value
SET MYPARA = MYCOMM.CREATEPARAMETER(RETURN,2,4)
MYCOMM.PARAMETERS.APPEND MYPARA
MYCOMM.EXECUTE
'Get the return value
DIM RETVALUE
RETVALUE = MYCOMM(0) 'or RETVALUE = MYCOMM.PARAMETERS(0)
SET MYCOMM = NOTHING
In MYCOMM.CREATEPARAMETER(RETURN,2,4), the meaning of each parameter is as follows:
The first parameter (RETURE) is the parameter name. The parameter name can be set arbitrarily, but generally it should be the same as the parameter name declared in the stored process. Here is the return value, I usually set it to RETURE;
The second parameter (2) indicates the data type of the parameter. Please refer to the ADO reference for specific type codes. Common type codes are given below:
ADBIGINT: 20;
ADBINARY: 128;
ADBOOLEAN: 11;
ADCHAR: 129;
ADDBTIMESTAMP: 135;
ADEMPTY: 0;
ADINTEGER: 3;
ADSMALLINT: 2;
ADTINYINT: 16;
ADVARCHAR: 200;
For the return value, only integer values can be taken, and -1 to -99 are reserved values;
The third parameter (4) indicates the nature of the parameter, where 4 indicates that this is a return value. The description of the value of this parameter is as follows:
0: The type cannot be determined; 1: Input parameter; 2: Input parameter; 3: Input or output parameter; 4: Return value
The ASP code given above should be said to be the complete code, that is, the most complex code. In fact,
SET MYPARA = MYCOMM.CREATEPARAMETER(RETURN,2,4)
MYCOMM.PARAMETERS.APPEND MYPARA
can be simplified to
MYCOMM.PARAMETERS.APPEND MYCOMM.CREATEPARAMETER(RETURN,2,4)
It can even be simplified further, as will be explained later.
For stored procedures with parameters, they can only be called using the COMMAND object (there is also information that they can be called through the CONNECTION object or RECORDSET object, but I have not tried it).
4. Stored process with input parameters and output parameters
The return value is actually a special output parameter. In most cases, we use a stored process that has both input and output parameters. For example, we want to obtain the user name of a user with a certain ID in the user information table. At this time, there is an input parameter----user ID. , and an output parameter----user name. The stored process that implements this function is as follows:
/*SP4*/
CREATE PROCEDURE DBO.GETUSERNAME
@USERIDINT,
@USERNAME VARCHAR(40) OUTPUT
AS
SET NOCOUNT ON
BEGIN
IF @USERID IS NULL RETURN
SELECT @USERNAME=USERNAME
FROM DBO.[USERINFO]
WHERE USERID=@USERID
RETURN
END
GO
The ASP code that calls the stored process is as follows:
'**Call a stored process with input and output parameters**
DIM MYCOMM,USERID,USERNAME
USERID=1
SET MYCOMM = SERVER.CREATEOBJECT(ADODB.COMMAND)
MYCOMM.ACTIVECONNECTION = MYCONSTR 'MYCONSTR is the database connection string
MYCOMM.COMMANDTEXT = GETUSERNAME 'Specify the stored process name
MYCOMM.COMMANDTYPE = 4 'Indicates that this is a stored process
MYCOMM.PREPARED = TRUE 'Require SQL commands to be compiled first
'Declare parameters
MYCOMM.PARAMETERS.APPEND MYCOMM.CREATEPARAMETER(@USERID,3,1,4,USERID)
MYCOMM.PARAMETERS.APPEND MYCOMM.CREATEPARAMETER(@USERNAME,200,2,40)
MYCOMM.EXECUTE
'Get the parameters
USERNAME = MYCOMM(1)
SET MYCOMM = NOTHING
In the above code, you can see that unlike declaring a return value, 5 parameters are required when declaring input parameters, and 4 parameters are required when declaring output parameters. When declaring input parameters, the five parameters are: parameter name, parameter data type, parameter type, data length, and parameter value. When declaring an output parameter, there is no last parameter: the parameter value.
Special attention should be paid to: when declaring parameters, the order must be the same as that defined in the storage process, and the data type and length of each parameter must also be the same as those defined in the storage process.
If the stored process has multiple parameters, the ASP code will appear cumbersome. You can use the WITH command to simplify the code:
'**Call a stored process with input and output parameters (simplified code)**
DIM MYCOMM,USERID,USERNAME
USERID=1
SET MYCOMM = SERVER.CREATEOBJECT(ADODB.COMMAND)
WITH MYCOMM
.ACTIVECONNECTION = MYCONSTR 'MYCONSTR is the database connection string
.COMMANDTEXT = GETUSERNAME 'Specify the stored process name
.COMMANDTYPE = 4 'Indicates that this is a stored process
.PREPARED = TRUE 'Require SQL commands to be compiled first
.PARAMETERS.APPEND .CREATEPARAMETER(@USERID,3,1,4,USERID)
.PARAMETERS.APPEND .CREATEPARAMETER(@USERNAME,200,2,40)
.EXECUTE
END WITH
USERNAME = MYCOMM(1)
SET MYCOMM = NOTHING
If we want to get the usernames of 10 users with IDs 1 to 10, do we need to create COMMAND objects 10 times? No. If you need to call the same stored process multiple times, just change the input parameters and you will get different output:
'**Multiple calls to the same stored process**
DIM MYCOMM,USERID,USERNAME
USERNAME=
SET MYCOMM = SERVER.CREATEOBJECT(ADODB.COMMAND)
FOR USERID = 1 TO 10
WITH MYCOMM
.ACTIVECONNECTION = MYCONSTR 'MYCONSTR is the database connection string
.COMMANDTEXT = GETUSERNAME 'Specify the stored process name
.COMMANDTYPE = 4 'Indicates that this is a stored process
.PREPARED = TRUE 'Require SQL commands to be compiled first
IF USERID = 1 THEN
.PARAMETERS.APPEND .CREATEPARAMETER(@USERID,3,1,4,USERID)
.PARAMETERS.APPEND .CREATEPARAMETER(@USERNAME,200,2,40)
.EXECUTE
ELSE
'Reassign values to the input parameters (input parameters and output parameters whose parameter values do not change at this time do not need to be re-declared)
.PARAMETERS(@USERID) = USERID
.EXECUTE
END IF
END WITH
USERNAME = USERNAME + MYCOMM(1) + , 'Maybe you like to use array storage
NEXT
SET MYCOMM = NOTHING
As can be seen from the above code: when calling the same stored process repeatedly, you only need to reassign the input parameters whose values have changed. This method has multiple input and output parameters, and only one input parameter value is called each time. When changes occur, the amount of code can be significantly reduced.
5. A stored process with return values, input parameters, and output parameters at the same time
As mentioned earlier, when calling a stored process, the order in which parameters are declared must be the same as the order defined in the stored process. Another point to pay special attention to: if the stored process has both a return value and input and output parameters, the return value must be declared first.
To demonstrate the calling method in this case, let's improve the above example. Still get the user name of the user with ID 1, but it is possible that the user does not have it (the user has been deleted, and USERID is a self-increasing field). The stored process returns different values depending on whether the user has it or not. At this point, the stored process and ASP code are as follows:
/*SP5*/
CREATE PROCEDURE DBO.GETUSERNAME
--In order to deepen the impression of the order, reverse the order of definition of the following two parameters.
@USERNAME VARCHAR(40) OUTPUT,
@USERIDINT
AS
SET NOCOUNT ON
BEGIN
IF @USERID IS NULL RETURN
SELECT @USERNAME=USERNAME
FROM DBO.[USERINFO]
WHERE USERID=@USERID
IF @@ROWCOUNT>0
RETURN 1
ELSE
RETURN 0
RETURN
END
GO
'**Call a stored process with return value, input parameters, and output parameters**
DIM MYCOMM,USERID,USERNAME
USERID=1
SET MYCOMM = SERVER.CREATEOBJECT(ADODB.COMMAND)
WITH MYCOMM
.ACTIVECONNECTION = MYCONSTR 'MYCONSTR is the database connection string
.COMMANDTEXT = GETUSERNAME 'Specify the stored process name
.COMMANDTYPE = 4 'Indicates that this is a stored process
.PREPARED = TRUE 'Require SQL commands to be compiled first
'The return value must be declared first
.PARAMETERS.APPEND .CREATEPARAMETER(RETURN,2,4)
'The declaration order of the following two parameters is also reversed accordingly.
.PARAMETERS.APPEND .CREATEPARAMETER(@USERNAME,200,2,40)
.PARAMETERS.APPEND .CREATEPARAMETER(@USERID,3,1,4,USERID)
.EXECUTE
END WITH
IF MYCOMM(0) = 1 THEN
USERNAME = MYCOMM(1)
ELSE
USERNAME = This user does not have
END IF
SET MYCOMM = NOTHING
6. Stored process that returns parameters and recordset at the same time
Sometimes, we need the storage process to return parameters and record sets at the same time. For example, when using the storage process for paging, we need to return parameters such as record sets and total data volume at the same time. The following is a stored process for paging:
/*SP6*/
CREATE PROCEDURE DBO.GETUSERLIST
@IPAGECOUNT INT OUTPUT, --Total number of pages
@IPAGE INT, --Current page number
@IPAGESIZE INT --Number of records per page
AS
SET NOCOUNT ON
BEGIN
--Create temporary table
CREATE TABLE #T (ID INT IDENTITY, --auto-increment field
USERID INT,
USERNAME VARCHAR(40))
--Write data to temporary table
INSERT INTO #T
SELECT USERID,USERNAME FROM DBO.[USERINFO]
ORDER BY USERID
--Get the total number of records
DECLARE @IRECORDCOUNT INT
SET @IRECORDCOUNT = @@ROWCOUNT
--Determine the total number of pages
IF @IRECORDCOUNT%@IPAGESIZE=0
SET @IPAGECOUNT=CEILING(@IRECORDCOUNT/@IPAGESIZE)
ELSE
SET @IPAGECOUNT=CEILING(@IRECORDCOUNT/@IPAGESIZE)+1
--If the requested page number is greater than the total number of pages, the last page will be displayed
IF @IPAGE > @IPAGECOUNT
SELECT @IPAGE = @IPAGECOUNT
--Determine the beginning and end records of the current page
DECLARE @ISTART INT --START RECORD
DECLARE @IEND INT --END RECORD
SELECT @ISTART = (@IPAGE - 1) * @IPAGESIZE
SELECT @IEND = @ISTART + @IPAGESIZE + 1
--Get the current page record
SELECT * FROM #T WHERE ID>@ISTART AND ID<@IEND
--Delete temporary table
DROP TABLE #T
--Return the total number of records
RETURN @IRECORDCOUNT
END
GO
In the above storage process, enter the current page number and the number of records per page, and return the record set of the current page, the total number of pages, and the total number of records. To be more typical, the total number of records is returned as a return value. The following is the ASP code that calls the stored process (the specific paging operation is omitted):
'**Call paging storage process**
DIM PAGENOW,PAGESIZE,PAGECOUNT,RECORDCOUNT
DIM MYCOMM,MYRST
PAGENOW = REQUEST(PN)
'Custom function used to verify natural numbers
IF CHECKNAR(PAGENOW) = FALSE THEN PAGENOW = 1
PAGESIZE = 20
SET MYCOMM = SERVER.CREATEOBJECT(ADODB.COMMAND)
WITH MYCOMM
.ACTIVECONNECTION = MYCONSTR 'MYCONSTR is the database connection string
.COMMANDTEXT = GETUSERLIST 'Specify the stored process name
.COMMANDTYPE = 4 'Indicates that this is a stored process
.PREPARED = TRUE 'Require SQL commands to be compiled first
'Return value (total number of records)
.PARAMETERS.APPEND .CREATEPARAMETER(RETURN,2,4)
'Output parameters (total number of pages)
.PARAMETERS.APPEND .CREATEPARAMETER(@IPAGECOUNT,3,2)
'Input parameters (current page number)
.PARAMETERS.APPEND .CREATEPARAMETER(@IPAGE,3,1,4,PAGENOW)
'Input parameters (number of records per page)
.PARAMETERS.APPEND .CREATEPARAMETER(@IPAGESIZE,3,1,4,PAGESIZE)
SET MYRST = .EXECUTE
END WITH
IF MYRST.STATE = 0 THEN 'No data was obtained, MYRST is closed
RECORDCOUNT = -1
ELSE
MYRST.CLOSE 'Note: To obtain parameter values, you need to close the recordset object first
RECORDCOUNT = MYCOMM(0)
PAGECOUNT = MYCOMM(1)
IF CINT(PAGENOW)>=CINT(PAGECOUNT) THEN PAGENOW=PAGECOUNT
END IF
SET MYCOMM = NOTHING
'Display records below
IF RECORDCOUNT = 0 THEN
RESPONSE.WRITE No record
ELSEIF RECORDCOUNT > 0 THEN
MYRST.OPEN
DO UNTIL MYRST.EOF
...
LOOP
'The following displays paging information
...
ELSE 'RECORDCOUNT=-1
RESPONSE.WRITE parameter error
END IF
Regarding the above code, there is only one point that needs to be explained: when returning the recordset and parameters at the same time, if you want to obtain the parameters, you need to close the recordset first, and then open it when using the recordset.
7. Stored process that returns multiple recordsets
This article first introduces the stored process that returns a recordset. Sometimes, a stored process needs to return multiple record sets. In ASP, how to obtain these record sets at the same time? In order to illustrate this problem, add two fields to the USERINFO table: USERTEL and USERMAIL, and set that only logged-in users can view these two contents.
/*SP7*/
CREATE PROCEDURE DBO.GETUSERINFO
@USERIDINT,
@CHECKLOGIN BIT
AS
SET NOCOUNT ON
BEGIN
IF @USERID IS NULL OR @CHECKLOGIN IS NULL RETURN
SELECT USERNAME
FROM DBO.[USRINFO]
WHERE USERID=@USERID
--If you are a logged in user, take USERTEL and USERMAIL
IF @CHECKLOGIN=1
SELECT USERTEL,USERMAIL
FROM DBO.[USERINFO]
WHERE USERID=@USERID
RETURN
END
GO
The following is the ASP code:
'**Call a stored process that returns multiple recordsets**
DIM CHECKLG,USERID,USERNAME,USERTEL,USERMAIL
DIM MYCOMM,MYRST
USERID=1
'CHECKLOGIN() is a custom function to determine whether the visitor is logged in
CHECKLG = CHECKLOGIN()
SET MYCOMM = SERVER.CREATEOBJECT(ADODB.COMMAND)
WITH MYCOMM
.ACTIVECONNECTION = MYCONSTR 'MYCONSTR is the database connection string
.COMMANDTEXT = GETUSERINFO 'Specify the stored process name
.COMMANDTYPE = 4 'Indicates that this is a stored process
.PREPARED = TRUE 'Requires SQL commands to be compiled first
.PARAMETERS.APPEND .CREATEPARAMETER(@USERID,3,1,4,USERID)
.PARAMETERS.APPEND .CREATEPARAMETER(@CHECKLOGIN,11,1,1,CHECKLG)
SET MYRST = .EXECUTE
END WITH
SET MYCOMM = NOTHING
'Get the value from the first record set
USERNAME = MYRST(0)
'Get the value from the second record set
IF NOT MYRST IS NOTHING THEN
SET MYRST = MYRST.NEXTRECORDSET()
USERTEL = MYRST(0)
USERMAIL = MYRST(1)
END IF
SET MYRST = NOTHING
In the above code, the NEXTRECORDSET method of the RECORDSET object is used to obtain multiple record sets returned by the storage process.
So far, this article has given a relatively comprehensive explanation of various situations in which ASP calls stored processes. Finally, let's talk about the different methods of calling multiple stored processes in an ASP program.
In an ASP program, at least the following three methods are feasible to call multiple stored processes:
1. Create multiple COMMAND objects
DIMMYCOMM
SET MYCOMM = SERVER.CREATEOBJECT(ADODB.COMMAND)
'Call stored process one
...
SET MYCOMM = NOTHING
SET MYCOMM = SERVER.CREATEOBJECT(ADODB.COMMAND)
'Call stored process two
...
SET MYCOMM = NOTHING
...
2. Create only one COMMAND object and clear its parameters when ending a call.
DIMMYCOMM
SET MYCOMM = SERVER.CREATEOBJECT(ADODB.COMMAND)
'Call stored process one
.....
'Clear parameters (assuming there are three parameters)
MYCOMM.PARAMETERS.DELETE 2
MYCOMM.PARAMETERS.DELETE 1
MYCOMM.PARAMETERS.DELETE 0
'Call stored process two and clear parameters
...
SET MYCOMM = NOTHING
At this time, please note: the order of clearing parameters is opposite to the order of parameter declaration. I don’t know the reason.
3. Use the REFRESH method of the PARAMETERS data collection to reset the PARAMETER object
DIMMYCOMM
SET MYCOMM = SERVER.CREATEOBJECT(ADODB.COMMAND)
'Call stored process one
.....
'Reset all PARAMETER objects contained in the PARAMETERS data collection
MYCOMM.PARAMETERS.REFRESH
'Call stored process two
.....
SET MYCOMM = NOTHING
It is generally believed that repeatedly creating objects is a less efficient method, but after testing (the test tool is MICROSOFT APPLICATION CENTER TEST), the results are unexpected:
Method 2 >= Method 1 >> Method 3
The running speed of method 2 is greater than or equal to method 1 (up to about 4% higher). The running speed of these two methods is much faster than method 3 (up to 130%). Therefore, it is recommended to use method 1 when there are many parameters. If not, use method 2.
It took me a day to finally put into writing some of my superficial experience in calling stored procedures in ASP. Among them, some of them I only know the effects but not the causes, and some may be wrong, but these are all through my personal practice. Please accept it critically, readers. If you have any different opinions, please let me know. Thanks in advance.