Full access to stored procedure applications during ASP development _ ASP skills

 

There are a lot of articles about ASP and stored procedures, but I doubt whether the authors have actually practiced it. I checked a large amount of relevant materials at the beginning and found that many of the methods provided in them were not the same in practice. For simple applications, these materials may be helpful, but they are limited to this, because they are all the same, copy each other, and use a little more complex.

Now, I basically access SQL Server by calling the stored procedure. The following words are a summary of the practices and hope to help you.

A stored procedure is one or more SQL commands stored as executable objects in the database.

Definition is always abstract. The stored procedure is actually a set of SQL statements that can complete certain operations, but these statements are stored in the database (Here we only talk about SQL Server ). If we create a stored procedure and call it in ASP, we can avoid mixing SQL statements with ASP code. There are at least three advantages:

First, greatly improve efficiency. Stored procedures are executed very quickly, and calling stored procedures can greatly reduce the number of interactions with the database.

Second, improve security. If SQL statements are mixed in ASP code, once the code is out of password, it also means that the database structure is out of password.

Third, it is conducive to the reuse of SQL statements.

In ASP, the stored procedure is generally called through the command object. Based on different situations, this article also introduces other calling methods. For convenience, the following simple classification is made based on the input and output of the stored procedure:

1. Only the stored procedure of a single record set is returned.

Suppose there are the following stored procedures (the purpose of this article is not to describe the T-SQL syntax, so the stored procedure only gives code, not to mention ):

/* SP1 */

Create procedure DBO. getuserlist

As

Set nocount on

Begin

Select * From DBO. [userinfo]

End

Go

The above Stored Procedure Retrieves all records in the userinfo table and returns a record set. The ASP code for calling the stored procedure through the command object is as follows:

** Call the stored procedure through the command object **

Dim mycomm, myrst

Set mycomm = server. Createobject ("ADODB. Command ")

Mycomm. activeconnection = myconstr is the database connection string

Mycomm. commandtext = "getuserlist" specifies the name of the stored procedure

Mycomm. commandtype = 4 indicates that this is a stored procedure

Mycomm. Prepared = true requires that the SQL command be compiled first

Set myrst = mycomm. Execute

Set mycomm = nothing

The set of records obtained by the stored procedure is assigned to myrst. Next, you can operate on myrst.

In the preceding code, the commandtype attribute indicates the request type. The values and descriptions are as follows:

-1 indicates that the commandtext parameter type cannot be determined

1 indicates that commandtext is a common command type

2. The commandtext parameter is an existing table name.

4. The commandtext parameter is the name of a stored procedure.

You can also call the stored procedure through the connection object or recordset object. The methods are as follows:

** Call the stored procedure through the connection object **

Dim myconn, myrst

Set myconn = server. Createobject ("ADODB. Connection ")

Myconn. Open myconstr is the database connection string

Set myrst = myconn. Execute ("getuserlist ",)

Set myconn = nothing

** Call the stored procedure through the recordset object **

Dim myrst

Set myrst = server. Createobject ("ADODB. recordset ")

Myrst. Open "getuserlist", myconstr, 0, 1, 4

Myconstr is the database connection string. The last parameter is of the same meaning as commandtype.

2. Stored Procedure without input/output

See the following stored procedure:

/* SP2 */

Create procedure DBO. deluserall

As

Set nocount on

Begin

Delete from DBO. [userinfo]

End

Go

In this stored procedure, all records in the userinfo table are deleted without any input or output. The Calling method is basically the same as described above, but the record set is not required:

** Call the stored procedure through the command object **

Dim mycomm

Set mycomm = server. Createobject ("ADODB. Command ")

Mycomm. activeconnection = myconstr is the database connection string

Mycomm. commandtext = "deluserall" specifies the name of the stored procedure

Mycomm. commandtype = 4 indicates that this is a stored procedure

Mycomm. Prepared = true requires that the SQL command be compiled first

Mycomm. Execute no need to retrieve record sets here

Set mycomm = nothing

Of course, this type of stored procedure can also be called through the connection object or recordset object, but the recordset object is created to obtain the record set. If no record set is returned, use the command object.

3. Stored Procedures with returned values

When performing SP2-like operations, SQL Server's powerful transaction processing functions should be fully utilized to maintain data consistency. In addition, we may need to return the execution status of the stored procedure. For this reason, 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

In the preceding stored procedure, 1 is returned when the delete operation is executed successfully. Otherwise, 0 is returned and the rollback operation is performed. To obtain the return value in ASP, you must use the parameters set to declare the parameter:

** Call a stored procedure with a returned value and obtain the returned value **

Dim mycomm, mypara

Set mycomm = server. Createobject ("ADODB. Command ")

Mycomm. activeconnection = myconstr is the database connection string

Mycomm. commandtext = "deluserall" specifies the name of the stored procedure

Mycomm. commandtype = 4 indicates that this is a stored procedure

Mycomm. Prepared = true requires that the SQL command be compiled first

Declared Return Value

Set mypara = mycomm. createparameter ("return", 2, 4)

Mycomm. Parameters. append mypara

Mycomm. Execute

Return Value

Dim retvalue

Retvalue = mycomm (0) or retvalue = mycomm. parameters (0)

Set mycomm = nothing

In mycomm. createparameter ("return", 2, 4), the meanings of parameters are as follows:

The first parameter ("Reture") is the parameter name. The parameter name can be set arbitrarily, but it should be the same as the parameter name declared in the stored procedure. Here is the return value. I used to set it to "Reture ";

The second parameter (2) indicates the Data Type of the parameter. For details about the type code, see the ADO reference. The following describes the common type code:

Adbigint: 20;

Adbinary: 128;

Adboolean: 11;

Adchar: 129;

Addbtimestamp: 135;

Adempty: 0;

Adinteger: 3;

Adsmallint: 2;

Adtinyint: 16;

Advarchar: 200;

For return values, only integer values 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 value of this parameter is described as follows:

0: The type cannot be determined; 1: input parameter; 2: input parameter; 3: input or output parameter; 4: Return Value

The above ASP code should 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

Mycomm. Parameters. append mycomm. createparameter ("return", 2, 4)

It can even be simplified and will be explained later.

For stored procedures with parameters, you can only use the command object to call (it can also be called through the connection object or recordset object, but I have not tried it ).

4. Stored Procedures with Input and Output Parameters

The returned value is actually a special output parameter. In most cases, we use a stored procedure with both input and output parameters. For example, if we want to obtain the username of an ID in the user information table, there is an input parameter ---- user ID, and an output parameter ---- user name. The stored procedure for implementing this function is as follows:

/* SP4 */

Create procedure DBO. GetUserName

@ 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 for calling the stored procedure is as follows:

** Call a stored procedure with input and output parameters **

Dim mycomm, userid, username

Userid = 1

Set mycomm = server. Createobject ("ADODB. Command ")

Mycomm. activeconnection = myconstr is the database connection string

Mycomm. commandtext = "GetUserName" specifies the name of the stored procedure

Mycomm. commandtype = 4 indicates that this is a stored procedure

Mycomm. Prepared = true requires that the SQL command be compiled first

Declare Parameters

Mycomm. Parameters. append mycomm. createparameter ("@ userid", 3, 1, 4, userid)

Mycomm. Parameters. append mycomm. createparameter ("@ username", 40)

Mycomm. Execute

Get output parameter

Username = mycomm (1)

Set mycomm = nothing

In the above code, we can see that, unlike the declared return value, five parameters are required for declaring input parameters, and four parameters are required for 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 output parameters, there is no last parameter: parameter value.

Note: When declaring parameters, the order must be the same as that defined in the stored procedure, the data type and length of each parameter must be the same as that defined in the stored procedure.

If the stored procedure has multiple parameters, the ASP code is cumbersome. You can use the with command to simplify the Code:

** Call a stored procedure with input and output parameters (simplified code )**

Dim mycomm, userid, username

Userid = 1

Set mycomm = server. Createobject ("ADODB. Command ")

With mycomm

. Activeconnection = myconstr is the database connection string

. Commandtext = "GetUserName" specifies the name of the stored procedure

. Commandtype = 4 indicates that this is a stored procedure

. Prepared = true requires that the SQL command be compiled first

. Parameters. append. createparameter ("@ userid", 3, 1, 4, userid)

. Parameters. append. createparameter ("@ username", 40)

. Execute

End

Username = mycomm (1)

Set mycomm = nothing

If we want to get the username with IDs of 1 to 10, 10 users, do we need to create 10 command objects? No. If you need to call the same stored procedure multiple times, you only need to change the input parameters to get different outputs:

** Multiple calls to the same stored procedure **

Dim mycomm, userid, username

Username = ""

Set mycomm = server. Createobject ("ADODB. Command ")

For userid = 1 to 10

With mycomm

. Activeconnection = myconstr is the database connection string

. Commandtext = "GetUserName" specifies the name of the stored procedure

. Commandtype = 4 indicates that this is a stored procedure

. Prepared = true requires that the SQL command be compiled first

If userid = 1 then

. Parameters. append. createparameter ("@ userid", 3, 1, 4, userid)

. Parameters. append. createparameter ("@ username", 40)

. Execute

Else

Assign a value to the input parameter again (in this case, the input parameter and the output parameter do not have to be declared again)

. Parameters ("@ userid") = userid

. Execute

End if

End

Username = username + mycomm (1) + "," Maybe you like to use Array Storage

Next

Set mycomm = nothing

The code above shows that when you call the same stored procedure repeatedly, you only need to assign a value to the input parameter whose value has changed. This method has multiple input and output parameters, when only one input parameter value changes during each call, the amount of code can be greatly reduced.

5. Stored Procedures with return values, input parameters, and output parameters

As mentioned above, when calling a stored procedure, the order of declared parameters must be the same as that defined in the stored procedure. Note: If a stored procedure has both return values and input and output parameters, the return values must be declared first.

To demonstrate the call method in this case, we can improve the above example. The username of the user whose ID is 1 is obtained, but the user may not exist (the user has been deleted, and the userid is a self-increasing field ). The stored procedure returns different values based on whether the user exists. The stored procedure and ASP Code are as follows:

/* SP5 */

Create procedure DBO. GetUserName

-- To enhance the impression of "sequence", the following two parameters are defined in reverse order.

@ 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

** Call a stored procedure with both return values, input parameters, and output parameters **

Dim mycomm, userid, username

Userid = 1

Set mycomm = server. Createobject ("ADODB. Command ")

With mycomm

. Activeconnection = myconstr is the database connection string

. Commandtext = "GetUserName" specifies the name of the stored procedure

. Commandtype = 4 indicates that this is a stored procedure

. Prepared = true requires that the SQL command be compiled first

The returned value must be declared first.

. Parameters. append. createparameter ("return", 2, 4)

The Declaration Order of the following two parameters is also reversed.

. Parameters. append. createparameter ("@ username", 40)

. Parameters. append. createparameter ("@ userid", 3, 1, 4, userid)

. Execute

End

If mycomm (0) = 1 then

Username = mycomm (1)

Else

Username = "this user does not exist"

End if

Set mycomm = nothing

6. Stored Procedures that return both parameters and Record Sets

Sometimes, we need a stored procedure to return both parameters and record sets. For example, when using Stored Procedure paging, we need to return parameters such as the record set and total data. The following is a stored procedure 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 a temporary table

Create Table # T (ID int identity, -- auto-increment Field

Userid int,

Username varchar (40 ))

-- Write data to the temporary table

Insert into # T

Select userid, username from DBO. [userinfo]

Order by userid

-- Retrieve 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 page number, the last page is displayed.

If @ ipage> @ ipagecount

Select @ ipage = @ ipagecount

-- Determine the start 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

-- Retrieve the current page record

Select * from # t where ID> @ istart and ID <@ iend

-- Delete a temporary table

Drop table # T

-- Total number of returned records

Return @ irecordcount

End

Go

In the preceding stored procedure, enter the current page number and the number of records on each page to return the record set, total page number, and total number of records on the current page. 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 procedure (the specific paging operation is omitted ):

** Call the paging Stored Procedure **

Dim pagenow, pagesize, pagecount, recordcount

Dim mycomm, myrst

Pagenow = request ("PN ")

User-defined functions are used to verify natural numbers.

If checknar (pagenow) = false then pagenow = 1

Pagesize = 20

Set mycomm = server. Createobject ("ADODB. Command ")

With mycomm

. Activeconnection = myconstr is the database connection string

. Commandtext = "getuserlist" specifies the name of the stored procedure

. Commandtype = 4 indicates that this is a stored procedure

. Prepared = true requires that the SQL command be compiled first

Return Value (total number of records)

. Parameters. append. createparameter ("return", 2, 4)

Output parameter (total number of pages)

. Parameters. append. createparameter ("@ ipagecount", 3, 2)

Input parameter (current page number)

. Parameters. append. createparameter ("@ ipage", 3, 1, 4, pagenow)

Input parameter (number of records per page)

. Parameters. append. createparameter ("@ ipagesize", 3, 1, 4, pagesize)

Set myrst =. Execute

End

If myrst. State = 0 then is not retrieved, myrst is disabled.

Recordcount =-1

Else

Myrst. Close Note: To obtain the parameter value, 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 records

If recordcount = 0 then

Response. Write "no record"

Elseif recordcount> 0 then

Myrst. Open

Do until myrst. EOF

......

Loop

The following page information is displayed:

......

Else recordcount =-1

Response. Write "parameter error"

End if

For the above Code, there is only one note: To obtain parameters when both the record set and parameters are returned, you must first disable the record set and enable it when using the record set.

7. Return the stored procedure of multiple Record Sets

This article first introduces the stored procedure of the returned record set. Sometimes, a stored procedure is required to return multiple record sets. In ASP, how can we get these record sets at the same time? To illustrate this problem, add two fields in the userinfo table: usertel and usermail, and set that only logon users can view the two fields.

/* Sp7 */

Create procedure DBO. getuserinfo

@ 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 it is a logon user, 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:

** Call the stored procedure that returns multiple record sets **

Dim checklg, userid, username, usertel, usermail

Dim mycomm, myrst

Userid = 1

Checklogin () is a custom function used to determine whether a visitor logs on.

Checklg = checklogin ()

Set mycomm = server. Createobject ("ADODB. Command ")

With mycomm

. Activeconnection = myconstr is the database connection string

. Commandtext = "getuserinfo" specifies the name of the stored procedure

. Commandtype = 4 indicates that this is a stored procedure

. Prepared = true requires that the SQL command be compiled first

. Parameters. append. createparameter ("@ userid", 3, 1, 4, userid)

. Parameters. append. createparameter ("@ checklogin", 11,1, 1, checklg)

Set myrst =. Execute

End

Set mycomm = nothing

Value from the first record set

Username = myrst (0)

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, multiple record sets returned by the stored procedure are obtained using the nextrecordset method of the recordset object.

So far, this article provides a comprehensive description of the various situations in which ASP calls the stored procedure. Finally, let's talk about how to call different methods of multiple stored procedures in an ASP program.

In an ASP program, it is feasible to call multiple stored procedures in at least three ways:

1. Create multiple command objects

Dim mycomm

Set mycomm = server. Createobject ("ADODB. Command ")

Call Stored Procedure 1

......

Set mycomm = nothing

Set mycomm = server. Createobject ("ADODB. Command ")

Call Stored Procedure 2

......

Set mycomm = nothing

......

2. Create only one command object. When the call is completed, clear its parameters.

Dim mycomm

Set mycomm = server. Createobject ("ADODB. Command ")

Call Stored Procedure 1

.....

Clear parameters (assuming there are three parameters)

Mycomm. Parameters. Delete 2

Mycomm. Parameters. Delete 1

Mycomm. Parameters. Delete 0

Call Stored Procedure 2 and clear parameters

......

Set mycomm = nothing

Note: The order of clearing parameters is the opposite of that of parameter Declaration. I don't know why.

3. Reset the parameter object using the refresh method of the parameters data set

Dim mycomm

Set mycomm = server. Createobject ("ADODB. Command ")

Call Stored Procedure 1

.....

Resets all parameter objects contained in the parameters dataset.

Mycomm. Parameters. Refresh

Call Stored Procedure 2

.....

Set mycomm = nothing

It is generally considered that repeated object creation is a low-efficiency 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 4%). The running speed of these two methods is much higher than that of method 3 (up to 130%). Therefore, it is recommended that when there are many parameters, use method 1 and method 2 when there are few parameters.

It took me a day to finally build up some of my superficial experiences in calling stored procedures in ASP. Some of them are the ones that I only know but do not know, and some may be wrong. However, these are all my own practices. You can accept it with criticism. I have different opinions. I hope you will give me some advice. Thank you first.