Can anyone explain to me the RETURN_VALUE parameter?
I want to know what RETURN_VALUE means! I am stuck with this. How do I 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
a source to share
The MS SQL stored procedure can return return value
- this is separate from the output parameters.
The above code gives you access to this return value.
See MSDN (RETURN (Transact-SQL)) for details .
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 to Ret
1 or 2, depending on the passed 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
a source to share
This is the value that will be returned from the stored procedure.
From Using a Stored Procedure with Out Parameters
A SQL Server stored procedure that you can call that returns one or more OUT parameters, which the parameters stored in the procedure uses to return data back to the calling application.
From 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();
}
}
a source to share
I think the parameter name for the return value can be anything.
If your stored procedure returns a value, it can 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
a source to share