asked on

Need to use AddOut Parameter. Help on setting up the variables.

Need help below on how to get the gradecodedetailId and the status asw well.

[WebMethod(Description = "Get Grade Code Detail based on the grade name")]
public void GetGradeCodeDetailID(string GradeName, ref int GradeCodeDetailID, ref string Status)
{
string procedureName = "init_price.PRICE_LIST_REPORTING.get_grade_code_dtl_id";
try
{
Database db = DatabaseFactory.CreateDatabase("InitialPrices.Properties.Settings.ConnectionString");
DbCommand dbCommand = db.GetStoredProcCommand(procedureName);

db.AddInParameter(dbCommand, "p_grade_name", DbType.String, GradeNameGradeName);
db.AddOutParameter(dbCommand, "p_grade_code_dtl_id", DbType.Int32, 4);
db.AddOutParameter(dbCommand, "p_status", DbType.String, 7);
db.ExecuteNonQuery(dbCommand);

GradeCodeDetailID = Convert.ToInt32( dbCommand.Parameters[1].Value, CultureInfo.InvariantCulture);
Status = dbCommand.Parameters[2].Value;
}
catch (Exception ex)
{
throw (ex);
}

return;
}

PROCEDURE get_grade_code_dtl_id (p_grade_name            IN  grade_code_dtl.grade_name%TYPE,
                                 p_grade_code_dtl_id     OUT grade_code_dtl.grade_code_dtl_id%TYPE,
                                 p_status                OUT varchar2)
IS
 
p_temp_id grade_code_dtl.grade_code_dtl_id%TYPE;
 
BEGIN
     p_status := 'Success';
     SELECT GRADE_NAME
     INTO p_temp_id
     FROM INIT_PRICE.GRADE_CODE_DTL A
     WHERE A.GRADE_NAME = p_grade_name;
 
     EXCEPTION
        WHEN no_data_found THEN p_grade_code_dtl_id := -1;
        WHEN others THEN p_status := 'Failure: ' || SQLERRM;
END get_grade_code_dtl_id;

Open in new window

ajexpert

Some thougs
You are not assigning any value to p_status incase of NO_DATA_FOUND EXCEPTION and p_grade_code_dtl_id in case of OTHERS EXCEPTION

In short consider assigning values to both OUT variables in case of each exception

Hope this helps

mathieu_cupryk

ASKER

I am not sure what u mean I need to try see if this can work,

ajexpert

This is what I mean to convey

PROCEDURE get_grade_code_dtl_id (p_grade_name            IN  grade_code_dtl.grade_name%TYPE,
                                 p_grade_code_dtl_id     OUT grade_code_dtl.grade_code_dtl_id%TYPE,
                                 p_status                OUT varchar2)
IS
 
p_temp_id grade_code_dtl.grade_code_dtl_id%TYPE;
 
BEGIN
     p_status := 'Success';
     SELECT GRADE_NAME
     INTO p_temp_id
     FROM INIT_PRICE.GRADE_CODE_DTL A
     WHERE A.GRADE_NAME = p_grade_name;
 
     EXCEPTION
        WHEN no_data_found THEN 
        p_grade_code_dtl_id := -1;
        p_status := 'Failure : No Data Found ';
        WHEN others THEN 
        p_status := 'Failure: ' || SQLERRM;
        p_grade_code_dtl_id := -1;
END get_grade_code_dtl_id;

Open in new window

ajexpert

One more point
In case of successful execution you are assigning
p_status := 'Success';
but p_grade_code_dtl_id is null.

is p_temp_id = p_grade_code_dtl_id ?

ASKER CERTIFIED SOLUTION

ajexpert

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

mathieu_cupryk

ASKER

i need help on the parameter passing though.
I really appreciate your help.