Can someone explain me the parameter RETURN_VALUE ?

Question

Hello. I want to know what does RETURN_VALUE mean! I'm stuck at this thing. How to use RETURN_VALUE in a SQL Server stored procedure? thanks..

ASP:

Set cmdDB = Server.CreateObject("ADODB.Command")
With cmdDB
   .ActiveConnection = ADOConM
   .CommandText = "usp_jaljava_member_select"
   .CommandType = adCmdStoredProc
   .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 0)
   .Parameters.Append .CreateParameter("@TLoginName", adVarChar, adParamInput, 15, lcase(TLoginName))
   .Parameters.Append .CreateParameter("@TPassword", adVarChar, adParamInput, 20, TPassword)
   .Parameters.Append .CreateParameter("@retval", adVarChar, adParamOutput, 50)

' .Parameters.Append .CreateParameter("@TPinCode", adVarChar, adParamInput, 15,TPinCode) .Execute,,adExecuteNoRecords

   RetVal = .Parameters("@retval")
   Ret = Trim(.Parameters("RETURN_VALUE"))

   'Set .ActiveConnection = Nothing
End With
Set cmdDB = Nothing

UTid = RetVal

SQL Server stored procedure:

CREATE PROCEDURE usp_jaljava_member_select 
   @TLoginName varchar(15),
   @TPassword varchar(20),
   @retval varchar(50) OUTPUT
   --@TPinCode varchar(15)
AS

Answer 1

This is the value that would be returned from the Stored procedure.

From Using a Stored Procedure with Output Parameters

A SQL Server stored procedure that you can call is one that returns one or more OUT parameters, which are parameters that the stored procedure uses to return data back to the calling application.

From the link

CREATE PROCEDURE GetImmediateManager
   @employeeID INT,
   @managerID INT OUTPUT
AS
BEGIN
   SELECT @managerID = ManagerID 
   FROM HumanResources.Employee 
   WHERE EmployeeID = @employeeID
END

public static void executeStoredProcedure(Connection con) {
   try {
      CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
      cstmt.setInt(1, 5);
      cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
      cstmt.execute();
      System.out.println("MANAGER ID: " + cstmt.getInt(2));
   }
   catch (Exception e) {
      e.printStackTrace();
   }
}

Answer 2

A MS SQL stored procedure can return a return value - this is apart from output parameters.

The code above gives you access to this return value.

See MSDN for more information (RETURN (Transact-SQL)).

So, for the following stored procedure:

CREATE PROCEDURE checkstate @param varchar(11)
AS
   IF (SELECT StateProvince FROM Person.vAdditionalContactInfo WHERE ContactID = @param) = 'WA'
   RETURN 1
ELSE
   RETURN 2;
GO

The following code would set Ret to 1 or 2, depending on the passed in parameter and data:

Set cmdDB = Server.CreateObject("ADODB.Command")
With cmdDB
    .ActiveConnection = ADOConM
    .CommandText = "checkstate"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 0)
    .Parameters.Append .CreateParameter("@param", adVarChar, adParamInput, 11, "Contact")

    .Execute,,adExecuteNoRecords

    Ret = Trim(.Parameters("RETURN_VALUE"))

End With
Set cmdDB = Nothing

Answer 3

I think, the parameter name for return value could be anything.
If your stored procedure returns a value, it could be written as

CREATE PROCEDURE usp_jaljava_member_select 
@TLoginName varchar(15),
@TPassword varchar(20),
@retval varchar(50) OUTPUT
--@TPinCode varchar(15)
AS

BEGIN
declare @errorCodeInCaseOfAnError int

Return @errorCodeInCaseOfAnError
END