A stored process with a return value
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 storage process to return to execution, and to that 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 above stored process, when the delete is executed smoothly, returns 1, otherwise 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 the stored process with the return value and get the return value * *
DIM Mycomm,mypara
SET Mycomm = SERVER. CreateObject ("Adodb.command")
Mycomm. ActiveConnection = Myconstr ' myconstr is a database connection string
Mycomm.commandtext = "Deluserall" ' specifies the name of the stored process
Mycomm.commandtype = 4 ' indicates that this is a stored process
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 should generally be the same as the name of the parameter declared in the stored process. Here is the return value, my habit is 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 a stored process 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).