Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-06-16
6
Medium Priority
?
375 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

971 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