Definition is 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 (we will only talk about SQL SERVER here). If we create stored processes and call stored processes in ASP, we can avoid mixing SQL statements with ASP code. There are at least three benefits of doing this:
First, greatly improve efficiency. The storage process itself executes very quickly, and calling the stored process can greatly reduce the number of interactions with the database.
Second, improve safety. If SQL statements are mixed in ASP code, once the code is lost, it means that the library structure is lost.
Third, it is conducive to the reuse of SQL statements.
In ASP, the stored process is generally called through the COMMAND object. According to different situations, this article also introduces other calling methods. For convenience of explanation, the following simple classification is made based on the input and output of the stored process:
1. Only return stored processes with a single record set
Suppose there is the following stored process (the purpose of this article is not to describe the T-SQL syntax, so the stored process only gives code and does not explain):
/*SP1*/
CREATE PROCEDURE
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:
'**Calling process through COMMAND object**
DIM MYCOMM,MYRST
SET MYCOMM = ("")
= MYCONSTR 'MYCONSTR is a database connection string
= "GETUSERLIST" 'Specify the stored process name
= 4
= TRUE 'Require SQL command to be compiled first
SET MYRST =
SET MYCOMM = NOTHING
The record set obtained by the stored process is assigned to MYRST. Next, the MYRST can be operated.
In the above code, the COMMANDTYPE attribute indicates the type of the request, the value and description are as follows:
-1 Indicates that the type of 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
The stored process can also be called through the CONNECTION object or the RECORDSET object, the methods are as follows:
'**Calling process through CONNECTION object**
DIM MYCONN,MYRST
SET MYCONN = ("")
MYCONSTR
SET MYRST = ("GETUSERLIST",0,4) 'The meaning of the last parameter break is the same as COMMANDTYPE
SET MYCONN = NOTHING
'**Calling process through RECORDSET object**
DIM MYRST
SET MYRST = ("")
"GETUSERLIST",MYCONSTR,0,1,4
'MYCONSTR is a database connection string, and the last parameter break means the same as COMMANDTYPE
2. Storage process without input and output
Please see the following stored processes:
/*SP2*/
CREATE PROCEDURE
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, and the call method is basically the same as mentioned above, except that you do not need to obtain the record set:
'**Calling process through COMMAND object**
DIM MYCOMM
SET MYCOMM = ("")
= MYCONSTR 'MYCONSTR is a database connection string
= "DELUSERALL" 'Specify the stored process name
= 4
= TRUE 'Require SQL command to be compiled first
'No need to obtain record sets here
SET MYCOMM = NOTHING
Of course, such stored processes can also be called through the CONNECTION object or the RECORDSET object. However, the RECORDSET object is established to obtain the record set. If the record set is not returned, it is better to use the COMMAND object.
3. Stored processes with return values
When performing operations similar to SP2, SQL SERVER's powerful transaction processing functions should be fully utilized to maintain data consistency. Moreover, we may need to store the process to return the execution status. For this purpose, modify SP2 as follows:
/*SP3*/
CREATE PROCEDURE
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 successfully executed, otherwise it returns 0 and rollback operation is performed. In order to get the return value in ASP, the PARAMETERS set needs to be used to declare the parameters:
'**Calling the stored process with the return value and obtaining the return value**
DIM MYCOMM,MYPARA
SET MYCOMM = ("")
= MYCONSTR 'MYCONSTR is the database connection string
= "DELUSERALL" 'Specify the stored process name
= 4
= TRUE 'Require SQL command to be compiled first
'Declaration returns value
SET MYPARA = ("RETURN",2,4)
MYPARA
'Get the return value
DIM RETVALUE
RETVALUE = MYCOMM(0) 'or RETVALUE = (0)
SET MYCOMM = NOTHING
In ("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 it should generally be the same as the parameter name declared in the stored process. Here is the return value, which I set to "RETURE" in habit;
The second parameter (2) indicates the data type of the parameter. For the specific type code, please refer to the ADO reference. The following is the commonly used type code:
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 plastic can be taken, and -1 to -99 are reserved values;
The third parameter (4) indicates the nature of the parameter, here 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 parameters; 2: Input parameters; 3: Input or output parameters; 4: Return value
The ASP code given above should be said to be complete code, that is, the most complex code, in fact
SET MYPARA = ("RETURN",2,4)
MYPARA
It can be simplified to
("RETURN",2,4)
It can even be simplified and will be explained later.
For stored processes with parameters, they can only be called using COMMAND object (some data also say that they can be called through CONNECTION object or RECORDSET object, but I have not tried it).
4. Stored processes with input parameters and output parameters
The return value is actually a special output parameter. In most cases, we use a storage process that has both input and output parameters. For example, if we want to obtain the user name of a certain ID user in the user information table, at this time, there is an input parameter--user ID, and an output parameter--user name. The stored processes that implement this function are as follows:
/*SP4*/
CREATE PROCEDURE
@USERID INT,
@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:
'**Calling stored process with input and output parameters**
DIM MYCOMM,USERID,USERNAME
USERID = 1
SET MYCOMM = ("")
= MYCONSTR 'MYCONSTR is a database connection string
= "GETUSERNAME" 'Specify the stored process name
= 4
= TRUE 'Require SQL command to be compiled first
'Declare parameters
("@USERID",3,1,4,USERID)
("@USERNAME",200,2,40)
'Get out the parameters
USERNAME = MYCOMM(1)
SET MYCOMM = NOTHING
In the above code, we can see that unlike declaring the return value, 5 parameters are required when declaring the input parameters, and 4 parameters are required when declaring the output parameters. When declaring input parameters, the 5 parameters are: parameter name, parameter data type, parameter type, data length, and parameter value. When declaring the output parameter, there is no last parameter: parameter value.
It should be noted that when declaring parameters, the order must be the same as the order defined in the stored process, and the data type and length of each parameter must also be the same as defined in the stored process.
If the stored process has multiple parameters, the ASP code will appear cumbersome, and you can use the WITH command to simplify the code:
'**Calling stored process with input and output parameters (simplified code)**
DIM MYCOMM,USERID,USERNAME
USERID = 1
SET MYCOMM = ("")
WITH MYCOMM
.ACTIVECONNECTION = MYCONSTR 'MYCONSTR is the database connection string
.COMMANDTEXT = "GETUSERNAME" 'Specify the stored process name
.COMMANDTYPE = 4
.PREPARED �
. .CREATEPARAMETER("@USERID",3,1,4,USERID)
. .CREATEPARAMETER("@USERNAME",200,2,40)
.EXECUTE
END WITH
USERNAME = MYCOMM(1)
SET MYCOMM = NOTHING
If we want to obtain the username of the 1 to 10 users, should we create COMMAND object 10 times? No. If you need to call the same stored process multiple times, just change the input parameters and you will get different outputs:
'**Call the same stored process multiple times**
DIM MYCOMM,USERID,USERNAME
USERNAME = ""
SET MYCOMM = ("")
FOR USERID = 1 TO 10
WITH MYCOMM
ACTIVECONNECTION = MYCONSTR 'MYCONSTR is the database connection string
.COMMANDTEXT = "GETUSERNAME" 'Specify the stored process name
.COMMANDTYPE = 4
.PREPARED �
IF USERID = 1 THEN
. .CREATEPARAMETER("@USERID",3,1,4,USERID)
. .CREATEPARAMETER("@USERNAME",200,2,40)
.EXECUTE
ELSE
'Reassign the incoming parameter (the incoming parameter and outgoing parameter whose parameter value does not change at this time does not need to be redeclared)
.PARAMETERS("@USERID") = USERID
.EXECUTE
END IF
END WITH
USERNAME = USERNAME + MYCOMM(1) + "," 'Maybe you like to use array storage
NEXT
SET MYCOMM = NOTHING
From the above code, we can see that when the same stored process is repeatedly called, you only need to reassign the value to the input parameters whose value has changed. This method can greatly reduce the amount of code when there are multiple input and output parameters and only one input parameter's value changes every time the call is called.
5. Stored processes with return values, input parameters, and output parameters at the same time
As mentioned earlier, when calling a stored process, the order of declared parameters must be the same as the order defined in the stored process. Another thing to note is that if the stored process has both return value and input and output parameters, the return value must be declared first.
To demonstrate the call method in this case, let's improve the above example. It is still obtained the username of the user with ID 1, but it is possible that the user does not have (the user has been deleted and USERID is a self-growing field). The stored process returns different values depending on whether the user has or not. At this time, the stored process and ASP code are as follows:
/*SP5*/
CREATE PROCEDURE
--In order to deepen the impression of "order", reverse the definition order of the following two parameters
@USERNAME VARCHAR(40) OUTPUT,
@USERID INT
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
'**Calling a stored process with both return value, input parameters, and output parameters**
DIM MYCOMM,USERID,USERNAME
USERID = 1
SET MYCOMM = ("")
WITH MYCOMM
.ACTIVECONNECTION = MYCONSTR 'MYCONSTR is the database connection string
.COMMANDTEXT = "GETUSERNAME" 'Specify the stored process name
.COMMANDTYPE = 4
.PREPARED �
'The return value must be declared first
. .CREATEPARAMETER("RETURN",2,4)
'The declaration order of the following two parameters is also reversed accordingly
. .CREATEPARAMETER("@USERNAME",200,2,40)
. .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. The stored process that returns both the parameters and the record set
Sometimes, we need to store processes to return parameters and record sets at the same time. For example, when using stored processes to paging, we need to return parameters such as record set and total data. The following is a stored process for paging:
/*SP6*/
CREATE PROCEDURE
@IPAGECOUNT INT OUTPUT, --Total number of pages
@IPAGE INT, --Current page number
@IPAGESIZE INT
AS
SET NOCOUNT ON
BEGIN
--Create temporary tables
CREATE TABLE #T (ID INT IDENTITY, --Auto-increase field
USERID INT,
USERNAME VARCHAR(40))
--Write data to temporary tables
INSERT INTO #T
SELECT USERID,USERNAME FROM DBO.[USERINFO]
ORDER BY USERID
--Acquiring 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 whole record 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 to return the record set of the current page, the total number of pages and the total number of records. For more typicality, the total number of records is returned as a return value. The following is the ASP code that calls the stored process (specific paging operations are omitted):
'**Calling the paging storage process**
DIM PAGENOW,PAGESIZE,PAGECOUNT,RECORDCOUNT
DIM MYCOMM,MYRST
PAGENOW = REQUEST("PN")
'Custom functions are used to verify natural numbers
IF CHECKNAR(PAGENOW) = FALSE THEN PAGENOW = 1
PAGESIZE = 20
SET MYCOMM = ("")
WITH MYCOMM
.ACTIVECONNECTION = MYCONSTR 'MYCONSTR is the database connection string
.COMMANDTEXT = "GETUSERLIST" 'Specify the stored process name
.COMMANDTYPE = 4
.PREPARED = TRUE 'Require SQL commands to be compiled first
'Return value (total record amount)
. .CREATEPARAMETER("RETURN",2,4)
'Export parameters (total number of pages)
. .CREATEPARAMETER("@IPAGECOUNT",3,2)
'Enter parameter (current page number)
. .CREATEPARAMETER("@IPAGE",3,1,4,PAGENOW)
'Input parameters (number of records per page)
. .CREATEPARAMETER("@IPAGESIZE",3,1,4,PAGESIZE)
SET MYRST = .EXECUTE
END WITH
IF = 0 THEN 'No data was retrieved, MYRST is closed
RECORDCOUNT = -1
ELSE
'Note: To obtain parameter values, you must first close the record set object
RECORDCOUNT = MYCOMM(0)
PAGECOUNT = MYCOMM(1)
IF CINT(PAGENOW)>=CINT(PAGECOUNT) THEN PAGENOW=PAGECOUNT
END IF
SET MYCOMM = NOTHING
'The following display record
IF RECORDCOUNT = 0 THEN
"No record"
ELSEIF RECORDCOUNT > 0 THEN
DO UNTIL
......
LOOP
'The following displays the page information
......
ELSE 'RECORDCOUNT=-1
"Parameter error"
END IF
For the above code, there is only one thing to explain: when returning the record set and parameters at the same time, to obtain the parameters, you must first turn off the record set and turn it on when using the record set.
7. Returns stored processes with multiple recordsets
The first thing that this article introduces is the stored process that returns the record set. Sometimes, a stored process needs to return multiple record sets. In ASP, how to obtain these record sets at the same time? 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
@USERID INT,
@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, please use USERTEL and USERMAIL
IF @CHECKLOGIN=1
SELECT USERTEL,USERMAIL
FROM DBO.[USERINFO]
WHERE USERID=@USERID
RETURN
END
GO
The following is the ASP code:
'**Calling process returning 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 = ("")
WITH MYCOMM
.ACTIVECONNECTION = MYCONSTR 'MYCONSTR is the database connection string
.COMMANDTEXT = "GETUSERINFO" 'Specify the stored process name
.COMMANDTYPE = 4
.PREPARED �
. .CREATEPARAMETER("@USERID",3,1,4,USERID)
. .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 = ()
USERTEL = MYRST(0)
USERMAIL = MYRST(1)
END IF
SET MYRST = NOTHING
In the above code, multiple recordsets returned by the stored process are obtained using the NEXTRECORDSET method of the RECORDSET object.
At this point, this article has made a relatively comprehensive explanation of various situations in which ASP calls stored processes. Finally, let’s talk about different methods in an ASP program that call multiple stored processes.
In an ASP program, it is feasible to call multiple stored processes with at least the following three methods:
1. Create multiple COMMAND objects
DIM MYCOMM
SET MYCOMM = ("")
'Call stored process one
......
SET MYCOMM = NOTHING
SET MYCOMM = ("")
'Call stored process two
......
SET MYCOMM = NOTHING
......
2. Create only one COMMAND object, and clear its parameters when one call is finished.
DIM MYCOMM
SET MYCOMM = ("")
'Call stored process one
.....
'Clear parameters (assuming there are three parameters)
2
1
0
'Call stored process two and clear parameters
......
SET MYCOMM = NOTHING
At this time, you should pay attention to: the order of clearing parameters is the opposite of the order of parameter declarations. I don’t know the reason.
3. Reset the PARAMETER object using the REFRESH method of the PARAMETERS data set
DIM MYCOMM
SET MYCOMM = ("")
'Call stored process one
.....
'Reset all PARAMETER objects contained in the PARAMETERS data collection
'Call stored process two
.....
SET MYCOMM = NOTHING
It is generally believed that repeatedly creating objects is a relatively inefficient method, but after testing (the test tool is MICROSOFT APPLICATION CENTER TEST), the results were unexpected:
Method 2 >= Method 1 >> Method 3
The running speed of method 2 is greater than or equal to Method 1 (maximum 4% higher), and the running speed of these two methods is much greater than Method 3 (maximum 130%). Therefore, it is recommended to use Method 1 when there are many parameters, and when there are fewer parameters, use Method 2.
It took a day to finally write down some of my superficial experiences in calling stored processes in ASP. Some of these are things that I only know the result but not the cause, and some may be wrong, but these are all practiced by me personally. Dear readers, please accept it critically. I have different opinions, I hope I will point me out, thank you in advance.