calling stored procedure with multiple IN/OUT parameters

Question

0.00/5 (No votes)

See more:

Hi all,
I have the store procedure as described below with four input fields and few output fields. I want to call this procedure from the method : dueDate(Request reqObj) { ... }
I want to log the data that is there in reqObj. the input filed described in stored procedure are defined in the Request class. How would be the syntax of the piece of code to call this stored procedure and log the input as well as output fields.

PROCEDURE vrP_GetOrderDetailsByMON
(
i_MASTERORDERNUM IN SERVICEORDER.MASTERORDERNUM%TYPE,
i_SERVICEORDERNUM IN SERVICEORDER.SERVICEORDERNUM%TYPE,
i_SERVICEORDERTYPE IN SERVICEORDER.SERVICEORDERTYPE%TYPE,
i_STATECODE IN SERVICEORDER.STATECODE%TYPE,

o_WIRECENTERID OUT SERVICEORDER.WIRECENTERID%TYPE,
o_CLASSOFSERVICENAME OUT SERVICEORDER.CLASSOFSERVICENAME%TYPE,
o_HSIIND OUT SERVICEORDERRESERVATION.UNITYIND%TYPE,
o_MCN OUT SERVICEORDER.MCN%TYPE,
o_SERVICECODEMODIFIER OUT SERVICEORDER.SERVICECODEMODIFIER%TYPE,
o_SERVICEADDRESS OUT SERVICEORDER.SERVICEADDRESS%TYPE,
o_EASTORDERDETAILSFLAG OUT CHAR,
o_WESTORDERDETAILSFLAG OUT CHAR,
--FTTPDETAILS--------
o_FTTPONTREQUIREDIND OUT SERVICEORDER.FTTPONTREQUIREDIND%TYPE,
o_FTTPENGINEERINGINTERVAL OUT SERVICEORDER.FTTPENGINEERINGINTERVAL%TYPE,
o_FTTPDROPTYPE OUT SERVICEORDER.FTTPDROPTYPE%TYPE,
o_FTTPONTTYPE OUT SERVICEORDER.FTTPONTTYPE%TYPE,

Posted 22-Jan-10 4:22am

jazy_smith

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Md. Marufuzzaman · Accepted Answer · 2010-01-24T02:43:00

Well nice question.... You need to use SqlParameter[] for executing this type of storedprocedure. The question is how this could be done... See the following code snippets..

(i) Input / Calling the StoredProcedure:

C#

for (iCount = 0; iCount <= 4; iCount++)
{
    oSQLParameter[iCount] = new SqlParameter();
    if (iCount == 4)
    // set the output parameter  
        oSQLParameter[iCount].Direction = ParameterDirection.Output;
    else 
    oSQLParameter[iCount].Direction = ParameterDirection.Input;
}
oSQLParameter[0].ParameterName = MyParama;
oSQLParameter[0].Size = 30;
oSQLParameter[0].Value = "Test";
..........
..........

//Call the ExecuteNonQueryStoredProcedure with the required parameters..
//Get the retValur
retValur= oSQLParameter[4].Value.ToString();

(ii) Executing the StoredProcedure:

C#

public int ExecuteNonQueryStoredProcedure(string strConnectionString, string strStoredProcedureName, SqlParameter[] _ObjSqlParameter)
        {
            //   declare variables
            SqlConnection _ObjConnection;
            SqlCommand _ObjCommand;
            int iRowsAffected;
            //   prepare a connection
            _ObjConnection = new SqlConnection(strConnectionString);
            //   open connection
            _ObjConnection.Open();
            //   prepare command
            _ObjCommand = new SqlCommand(strStoredProcedureName, _ObjConnection);
            _ObjCommand.CommandType = CommandType.StoredProcedure;

            //   asign parameters to command
            //SqlParameter collObj ;
            foreach (SqlParameter collObj in _ObjSqlParameter)
            {
                _ObjCommand.Parameters.Add(collObj);
            }

            // execute command
            iRowsAffected = _ObjCommand.ExecuteNonQuery();

            _ObjCommand.Dispose();
            _ObjConnection.Close();
            _ObjConnection.Dispose();

            //   return affected rows
            return iRowsAffected;
        }

Hope this may helped you... :)