使用多个IN/OUT参数调用存储过程 [英] calling stored procedure with multiple IN/OUT parameters

问题描述

大家好，
我有如下所述的存储过程，其中包含四个输入字段和几个输出字段.我想从方法中调用此过程:dueDate(Request reqObj){...}
我想记录reqObj中存在的数据.存储过程中描述的输入文件在Request类中定义.调用此存储过程并记录输入和输出字段的代码段的语法如何?


程序vrP_GetOrderDetailsByMON
(
i_MASTERORDERNUM IN SERVICEORDER.MASTERORDERNUM％TYPE，
i_SERVICEORDERNUM IN SERVICEORDER.SERVICEORDERNUM％TYPE，
在SERVICEORDER.SERVICEORDERTYPE％TYPE中的i_SERVICEORDERTYPE，
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.FTTPONTREQUIREDINDIND％TYPE，
o_FTTPENGINEERINGINTERVAL OUT SERVICEORDER.FTTPENGINEERINGINTERVAL％TYPE，
o_FTTPDROPTYPE OUT SERVICEORDER.FTTPDROPTYPE％TYPE，
o_FTTPONTTYPE OUT SERVICEORDER.FTTPONTTYPE％TYPE，

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,

推荐答案

很好的问题....您需要使用SqlParameter []来执行这种类型的存储过程.问题是如何做到这一点...请参见以下代码段..

(i)输入/调用存储过程:

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:

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)执行存储过程:

(ii) Executing the StoredProcedure:

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... :)

这篇关于使用多个IN/OUT参数调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！