• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

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

0
mathieu_cupryk
Asked:
mathieu_cupryk
  • 4
  • 2
1 Solution
 
ajexpertCommented:
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
0
 
mathieu_cuprykAuthor Commented:
I am not sure what u mean I need to try see if this can work,
0
 
ajexpertCommented:
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

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
ajexpertCommented:
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 ?    
0
 
ajexpertCommented:
I believe p_temp_id = p_grade_code_dtl_id looking at the type declaration

See the modified SP here
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;
 
     p_grade_code_dtl_id     := p_temp_id;
     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

0
 
mathieu_cuprykAuthor Commented:
i need help on the parameter passing though.
I really appreciate your help.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now