Solved

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

Posted on 2008-06-16
6
345 Views
Last Modified: 2013-12-07
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
Comment
Question by:mathieu_cupryk
  • 4
  • 2
6 Comments
 
LVL 14

Expert Comment

by:ajexpert
ID: 21798027
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
 

Author Comment

by:mathieu_cupryk
ID: 21798163
I am not sure what u mean I need to try see if this can work,
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 21798210
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 14

Expert Comment

by:ajexpert
ID: 21798247
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
 
LVL 14

Accepted Solution

by:
ajexpert earned 500 total points
ID: 21798263
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
 

Author Comment

by:mathieu_cupryk
ID: 21798523
i need help on the parameter passing though.
I really appreciate your help.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now