Stored procedures with return recordset-asp call stored procedure

Stored Procedures | The recordset assumes the following stored procedures (the purpose of this article is not to tell the T-SQL syntax, so the stored procedure gives only code, not 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)