0

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
Jon Adams
  • 24,464
  • 18
  • 82
  • 120
Fran Martinez
  • 677
  • 4
  • 15
  • 36

0 Answers0