Getting started with stored procedures under ASP, page 2/5

2. Stored procedures without input and output
Please see the following stored procedure:
/*SP2*/ 
CREATE PROCEDURE  
as 
     set nocount on 
begin 
     delete from dbo.[userinfo] 
end 
go 
This stored procedure 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:

Of course, such stored procedures can also be called through the Connection object or Recordset object. However, the Recordset object is created to obtain the record set. If the record set is not returned, it is better to use the Command object.

3. Stored procedures 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 the stored procedure to return the execution status. For this purpose, modify SP2 as follows:
/*SP3*/ 

The above stored procedure returns 1 when the delete is successfully executed, otherwise it returns 0 and performs a rollback operation. In order to get the return value in ASP, the Parameters collection needs to be used to declare the parameters:
'**Calling a stored procedure with a return value and obtaining the return value**
DIM MyComm,MyPara 
Set MyComm = ("") 
= MyConStr 'MyConStr is the database connection string
= "delUserAll" 'Specify the stored procedure name
= 4 ' indicates that this is a stored procedure
= true 'Require SQL command to be compiled first
'Declare the return 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 procedure. Here is the return value, which I am used to set to "RETURE";
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 shape 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 
Can be simplified to
 ("RETURN",2,4) 

It can even be simplified and will be explained later.
For stored procedures with parameters, you can only use Command object to call (some data also say that it can be called through Connection object or Recordset object, but I have not 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 a stored procedure with 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 procedure to implement this function is as follows:
/*SP4*/ 
CREATE PROCEDURE  
@UserID int, 
@UserName varchar(40) output 
as 
set nocount on