Tip: ASP development in the storage process application full contact

Stored Procedures | tips

ASP and stored procedures (Stored procedures) article a lot, but I doubt whether the authors really practice. I have consulted a lot of relevant data at the beginning of the class, and found that many of the methods provided are not the case. For simple applications, this information may be helpful, but it is limited to this, because they are simply stereotyped, mutual plagiarism, a slightly more complex application, all vague.

Now, I basically access SQL Server by calling stored procedures, the following text is a summary of the practice, I hope to be helpful to everyone.

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

Definitions are always abstract. A stored procedure is actually a set of SQL statements that can do something, except that the set of statements is in the database (here we talk about SQL Server). If we create stored procedures and call stored procedures 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 stored procedure itself executes very quickly, and invoking a stored procedure can significantly reduce the number of interactions with the database.

Second, improve security. If the SQL statement mixed in the ASP code, once the code compromised, but also means that the library structure compromised.

Third, to facilitate the reuse of SQL statements.

In ASP, the stored procedure is usually invoked through the command object, and the other invocation methods are described in this article according to different situations. To facilitate the description, according to the input and output of the stored procedure, make the following simple categories:

1. A stored procedure that returns only a single recordset

The following stored procedures are assumed (the purpose of this article is not to tell the T-SQL syntax, so the stored procedure gives the code only, not the description):

/*sp1*/

CREATE PROCEDURE Dbo.getuserlist

As

SET NOCOUNT ON

Begin

SELECT * FROM dbo. [UserInfo]

End

Go

The above stored procedure gets all the records in the UserInfo table and returns a recordset. The ASP code that invokes the stored procedure through the Command object is as follows:

' * * Call stored procedure via command object * *

DIM Mycomm,myrst

Set Mycomm = Server.CreateObject ("Adodb.command")

mycomm.activeconnection = Myconstr ' myconstr is a database connection string

Mycomm.commandtext = "Getuserlist" ' specifies stored procedure name

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

Mycomm.prepared = True ' requires that SQL commands be compiled in advance

Set Myrst = Mycomm.execute

Set Mycomm = Nothing

The recordset obtained by the stored procedure is assigned to Myrst, and then the Myrst can be manipulated.

In the above code, the CommandType property indicates the type of the request, and 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 generic command type

2 indicates that the CommandText parameter is a table name that exists

4 indicates that the CommandText parameter is the name of a stored procedure

You can also invoke stored procedures by connection objects or Recordset objects, respectively, by using the following methods:

' * * Invoke stored procedure via connection object * *

DIM Myconn,myrst

Set myconn = Server.CreateObject ("ADODB. Connection ")

MyConn.Open Myconstr ' myconstr is a database connection string

Set Myrst = Myconn.execute ("Getuserlist", 0,4) ' last argument meaning same as CommandType

Set myconn = Nothing

' * * Call stored procedure via Recordset object * *

DIM Myrst

Set Myrst = Server.CreateObject ("ADODB. Recordset ")

Myrst.open "Getuserlist", myconstr,0,1,4

' Myconstr is a database connection string, and the last argument has the same meaning as CommandType

2. Stored procedures without input and output

Please see the following stored procedures:

/*sp2*/

CREATE PROCEDURE Dbo.deluserall

As

SET NOCOUNT ON

Begin

Delete FROM dbo. [UserInfo]

End

Go

The stored procedure deletes all records in the UserInfo table, without any input or output, and the invocation method is essentially the same as the above, except that the recordset is not obtained:

' * * Call stored procedure via command object * *

DIM Mycomm

Set Mycomm = Server.CreateObject ("Adodb.command")

mycomm.activeconnection = Myconstr ' myconstr is a database connection string

Mycomm.commandtext = "Deluserall" ' specifies stored procedure name

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

Mycomm.prepared = True ' requires that SQL commands be compiled in advance

Mycomm.execute ' There's no need to get the recordset here again

Set Mycomm = Nothing

This type of stored procedure can, of course, be invoked by connection object or Recordset object, although the Recordset object is created to get the recordset, or to use the Command object if no Recordset is returned.

3. Stored procedures with return values

When doing something like SP2, you should take full advantage of SQL Server's powerful transaction processing capabilities to maintain data consistency. Also, we may need the stored procedure to return to execution, and to this end, modify the 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 stored procedure above returns 1 when the delete is executed smoothly, otherwise it returns 0 and rolls back the operation. In order to get the return value in ASP, you need to use the Parameters collection to declare the parameter:

' * * Call stored procedure with return value and get return value * *

DIM Mycomm,mypara

Set Mycomm = Server.CreateObject ("Adodb.command")

mycomm.activeconnection = Myconstr ' myconstr is a database connection string

Mycomm.commandtext = "Deluserall" ' specifies stored procedure name

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

Mycomm.prepared = True ' requires that SQL commands be compiled in advance

' 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 meanings of each parameter are as follows:

The first argument ("Reture") is the name of the parameter. Parameter names can be set arbitrarily, but generally should be the same as the name of the parameter declared in the stored procedure. Here is the return value, which I am accustomed to set to "Reture";

The second parameter (2) indicates the data type of the parameter, the specific type code refer to the ADO reference, and the following are the commonly used type codes:

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 the shape is taken, and-1 to-99 is the reserved value;

The third argument (4) indicates the property of the parameter, where 4 indicates that it is a return value. The value of this parameter is described as follows:

0: type cannot be determined; 1: input parameters; 2: input parameters; 3: input or output parameters; 4: return value

The above mentioned ASP code, should be said to be 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 continue to be simplified and will be explained later.

For stored procedures with parameters, it can only be invoked using the Command object (there is also data that can be invoked through the Connection object or Recordset object, but I haven't tried it).

4. Stored procedures with input parameters and output parameters

The return value is actually a special output parameter. In most cases, we use stored procedures that have both input and output parameters, such as the user name of an ID user in the User Information table, an input parameter----user ID, and an output parameter----user name. The stored procedures for implementing this feature are as follows:

/*sp4*/

CREATE PROCEDURE Dbo.getusername

@UserID int,

@UserName varchar () 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 invokes the stored procedure is as follows:

' * * Call stored procedures with input and OUTPUT parameters * *

DIM Mycomm,userid,username

UserID = 1

Set Mycomm = Server.CreateObject ("Adodb.command")

mycomm.activeconnection = Myconstr ' myconstr is a database connection string

Mycomm.commandtext = "GetUserName" ' specifies stored procedure name

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

Mycomm.prepared = True ' requires that SQL commands be compiled in advance

' 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 argument

UserName = Mycomm (1)

Set Mycomm = Nothing

In the above code, you can see that, unlike declaring a return value, you need 5 parameters to declare an input parameter, and 4 parameters to declare an output parameter. When declaring an input parameter, 5 parameters are: Parameter name, parameter data type, parameter type, data length, parameter value. When declaring an output parameter, there is no last argument: parameter value.

It is important to note that when declaring a parameter, the order must be the same as the order defined in the stored procedure, and the data type and length of each parameter will be the same as defined in the stored procedure.

If the stored procedure has more than one parameter, the ASP code becomes cumbersome, and you can use the WITH command to simplify the code:

' * * Call stored procedures 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 a database connection string

. CommandText = "GetUserName" ' specifies stored procedure name

. CommandType = 4 ' indicates that this is a stored procedure

. Prepared = True ' requires that SQL commands be compiled in advance

. 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 were to get a username of 1 to 10, 10 users, would you like to create a 10 command object? No. If you need to call the same stored procedure multiple times, you can get different output by simply changing the input parameters:

' * * Calls the same stored procedure multiple times * *

DIM Mycomm,userid,username

UserName = ""

Set Mycomm = Server.CreateObject ("Adodb.command")

For UserID = 1 to 10

With Mycomm

. ActiveConnection = Myconstr ' myconstr is a database connection string

. CommandText = "GetUserName" ' specifies stored procedure name

. CommandType = 4 ' indicates that this is a stored procedure

. Prepared = True ' requires that SQL commands be compiled in advance

If UserID = 1 Then

. Parameters.Append. CreateParameter ("@UserID", 3,1,4,userid)

. Parameters.Append. CreateParameter ("@UserName", 200,2,40)

. Execute

Else

' re-assigning values to the parameters (at which point the parameter values do not change and the argument does not have to be declared again)

. Parameters ("@UserID") = UserID

. Execute

End If

End With

UserName = UserName + mycomm (1) + "," ' Maybe you like to use array storage

Next

Set Mycomm = Nothing

As you can see from the above code: when you call the same stored procedure repeatedly, you only need to reassign the input parameter that changes the value, this method can greatly reduce the amount of code when there are multiple input and output parameters, and the value of only one input parameter changes at each call.

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

As mentioned earlier, when you call a stored procedure, the order in which you declare the parameters is the same as the order defined in the stored procedure. It is also important to note that if the stored procedure has both a return value and an input or output parameter, the return value is first declared.

To demonstrate the invocation method in this case, let's improve on the example above. Or get the user name of the user with ID 1, but it is possible that the user does not exist (the user has been deleted and UserID is the field that grew). Stored procedures return different values depending on whether the user is present or not. At this point, the stored procedure and ASP code are as follows:

/*sp5*/

CREATE PROCEDURE Dbo.getusername

--in order to deepen the impression of "order", the following two parameters are defined in reverse order

@UserName varchar () 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 stored procedures with return values, input parameters, output parameters * *

DIM Mycomm,userid,username

UserID = 1

Set Mycomm = Server.CreateObject ("Adodb.command")

With Mycomm

. ActiveConnection = Myconstr ' myconstr is a database connection string

. CommandText = "GetUserName" ' specifies stored procedure name

. CommandType = 4 ' indicates that this is a stored procedure

. Prepared = True ' requires that SQL commands be compiled in advance

' return value to be declared first

. Parameters.Append. CreateParameter ("return", 2,4)

' The declaration order of the following two parameters is also reversed

. 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 exist"

End If

Set Mycomm = Nothing

6. Return parameters and Recordset stored procedures at the same time

Sometimes, we need stored procedures to return both parameters and recordsets, such as when using a stored procedure paging, returning both the recordset and the total amount of data. The following is a stored procedure that handles paging:

/*sp6*/

CREATE PROCEDURE Dbo.getuserlist

@iPageCount int OUTPUT,--Total 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,--self-added field

UserID int,

Username varchar (40))

--write data to a temporary table

INSERT INTO #t

Select Userid,username from dbo. [UserInfo]

ORDER BY UserID

--Total number of records obtained

DECLARE @iRecordCount int

Set @iRecordCount = RowCount

--Determine total pages

IF @iRecordCount% @iPageSize =0

SET @iPageCount =ceiling (@iRecordCount/@iPageSize)

ELSE

SET @iPageCount =ceiling (@iRecordCount/@iPageSize) +1

--Displays the last page if the requested page number is greater than the total pages

IF @iPage > @iPageCount

SELECT @iPage = @iPageCount

--Determine the history of the current page

DECLARE @iStart int--start record

DECLARE @iEnd int--end record

SELECT @iStart = (@iPage-1) * @iPageSize

SELECT @iEnd = @iStart + @iPageSize + 1

--Take the current page record

SELECT * from #t where id> @iStart and ID < @iEnd

--Delete temporary tables

DROP TABLE #t

--Returns the total number of records

Return @iRecordCount

End

Go

In the previous stored procedure, enter the current page number and the number of records per page, returning the recordset, total pages, and total number of records for the current page. To be more typical, the total number of records is returned in the form of a return value. The following is the ASP code that invokes the stored procedure (omitted for specific paging operations):

' * * Call the paging stored procedure * *

DIM Pagenow,pagesize,pagecount,recordcount

DIM Mycomm,myrst

Pagenow = Request ("pn")

' Custom function to validate natural numbers

If Checknar (pagenow) = False Then Pagenow = 1

PageSize = 20

Set Mycomm = Server.CreateObject ("Adodb.command")

With Mycomm

. ActiveConnection = Myconstr ' myconstr is a database connection string

. CommandText = "getuserlist" ' specifies stored procedure name

. CommandType = 4 ' indicates that this is a stored procedure

. Prepared = True ' requires that SQL commands be compiled in advance

' Return value (Total records)

. Parameters.Append. CreateParameter ("return", 2,4)

' Out parameters (total pages)

. Parameters.Append. CreateParameter ("@iPageCount", 3,2)

' Enter parameter (current page number)

. Parameters.Append. CreateParameter ("@iPage", 3,1,4,pagenow)

' Enter parameter (number of records per page)

. Parameters.Append. CreateParameter ("@iPageSize", 3,1,4,pagesize)

Set Myrst =. Execute

End With

If myrst.state = 0 Then ' not taken to data, Myrst closed

RecordCount =-1

Else

Myrst.close ' NOTE: To get the value of a parameter, you close the Recordset object first

RecordCount = Mycomm (0)

PageCount = Mycomm (1)

If CInt (Pagenow) > =cint (pagecount) then Pagenow=pagecount

End If

Set Mycomm = Nothing

' Show records below

If RecordCount = 0 Then

Response.Write "No Record"

ElseIf RecordCount > 0 Then

Myrst.open

Do Until myrst.eof

......

Loop

' The following displays the paging information

......

Else ' Recordcount=-1

Response.Write "Parameter Error"

End If

For the above code, there is only one point to note: When you return both a recordset and a parameter, to get a parameter, you close the recordset, and then open the recordset when you use it.

7. Return stored procedures for multiple recordsets

This article first describes the stored procedures that return a recordset. Sometimes, you need a stored procedure to return multiple recordsets, how do you get them at the same time in the ASP? To illustrate this issue, add two fields to the UserInfo table: Usertel and Usermail, and set only the Logged-in users to see both.

/*sp7*/

CREATE PROCEDURE Dbo.getuserinfo

@userid int,

@checklogin bit

As

SET NOCOUNT ON

Begin

If @userid is null or @checklogin be null return

Select username

FROM dbo. [Usrinfo]

where userid= @userid

--If you are a registered 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 stored procedures that return multiple recordsets * *

DIM Checklg,userid,username,usertel,usermail

DIM Mycomm,myrst

UserID = 1

' Checklogin () is a custom function that determines whether the visitor is logged in

CHECKLG = Checklogin ()

Set Mycomm = Server.CreateObject ("Adodb.command")

With Mycomm

. ActiveConnection = Myconstr ' myconstr is a database connection string

. CommandText = "GetUserInfo" ' specifies stored procedure name

. CommandType = 4 ' indicates that this is a stored procedure

. Prepared = True ' requires that SQL commands be compiled in advance

. Parameters.Append. CreateParameter ("@userid", 3,1,4,userid)

. Parameters.Append. CreateParameter ("@checklogin", 11,1,1,CHECKLG)

Set Myrst =. Execute

End With

Set Mycomm = Nothing

' Take values from the first set of records

UserName = Myrst (0)

' Take values from the second recordset

If not Myrst are nothing Then

Set Myrst = Myrst.nextrecordset ()

Usertel = Myrst (0)

Usermail = Myrst (1)

End If

Set Myrst = Nothing

In the above code, a Recordset object's NextRecordset method is used to obtain multiple recordsets returned by the stored procedure.

So far, this article has made a more comprehensive explanation for the various cases of ASP calling stored procedures. And finally, in an ASP program, the different methods of calling multiple stored procedures.

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

1. Create multiple Command objects

DIM Mycomm

Set Mycomm = Server.CreateObject ("Adodb.command")

' Call a stored procedure

......

Set Mycomm = Nothing

Set Mycomm = Server.CreateObject ("Adodb.command")

' Call stored procedure two

......

Set Mycomm = Nothing

......

2. Create only one Command object and clear its arguments when the call is ended

DIM Mycomm

Set Mycomm = Server.CreateObject ("Adodb.command")

' Call a stored procedure

.....

' Clear parameters (assuming three parameters)

MyComm.Parameters.delete 2

MyComm.Parameters.delete 1

MyComm.Parameters.delete 0

' Call stored procedure two and clear parameters

......

Set Mycomm = Nothing

Note that the order of the purge parameters is the opposite of the order of the parameter declarations, and I don't know why.

3. Reset the Parameter object using the Refresh method of the Parameters data collection

DIM Mycomm

Set Mycomm = Server.CreateObject ("Adodb.command")

' Call a stored procedure

.....

' Resets all parameter objects contained in the Parameters data collection

MyComm.Parameters.Refresh

' Call stored procedure two

.....

Set Mycomm = Nothing

It is generally thought that creating objects repeatedly is a less efficient method, but after testing (the test tool is Microsoft Application Center test), the result is unexpected:

Method 2 > = Method 1 > > Method 3

Method 2 running speed is greater than or equal to Method 1 (up to 4% or so), the two methods are much faster than Method 3 (up to 130%), so the proposed parameters for a long time, using Method 1, in less than the parameter, the method 2.

It took me a day to finally put my words into some superficial experience of invoking stored procedures in ASP. Some of these are the only things I know about them, and some of them may be wrong, but these are the things I've practiced. Everyone reader to accept it critically. There are different opinions, I hope you will point out to me, thank you first.