I'm having trouble retrieving the output information from the stored procedure. My expected value is the number associated with the customer based on the customer name parameter value. The value I'm receiving is -1, however the customer name entered exists in the database.
I suspect the way I constructed the code behind is producing the unexpected value. How can I retrieve the outputCustomerNumber
value from the stored procedure when called through a DataManager
?
Note: I receive the expected value back when I execute this stored procedure in SQL Server Management Studio.
public int GetCustomerNumberOnName(string strCustomerName)
{
int customerNumber = 0;
using (DataManager dm = new DataManager())
{
dm.AddParameter("@strCustomerName", strCustomerName, SqlDbType.VarChar, 75, ParameterDirection.Input);
dm.AddParameter("@outCustomerNumber", SqlDbType.Int, int.MaxValue, ParameterDirection.Output);
customerNumber = dm.runSPNonQuery("GetCustomerNumberOnName");
}
return customerNumber;
}
Stored procedure:
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetCustomerNumberOnName]
@strCustomerName VarChar (75) = NULL,
@strCustomerNumber int = NULL,
--- Output parameter (Integer)
@outCustomerNumber int OUTPUT
AS
SET NOCOUNT ON;
SELECT
@outCustomerNumber = Customer.CMR_NUMBER
FROM
Customer_Information
INNER JOIN
Customer ON Customer_Information.CMR_ROWID = Customer.CMR_ROWID
WHERE
(Customer.CMR_NUMBER = @strCustomerNumber)
OR Customer_Information.CIN_NAME LIKE @strCustomerName
RETURN @outCustomerNumber