CODEWITHSUNDEEP
asp.netoracle-databasestored-proceduresplsql
Ishita
Ishita

Reputation: 41

How to get stored procedure's returning value?

I have made stored procedures in oracle.
I'm calling it through my asp.net code.
The procedure is : 

 PROCEDURE prc_GetNewQuestionNo(iNextQuestionNo IN OUT NUMBER)
 IS
    iQuestionNo NUMBER ;

 BEGIN
     Select MAX(QUESTIONNO)+ 1 INTO iQuestionNo
     from tblIFFCOQUESTIONMASTER;
     iNextQuestionNo:=iQuestionNo;
 END prc_GetNewQuestionNo;

and I'm calling it in asp.net:

<Connection>
  com.CommandType = CommandType.StoredProcedure;
                com.CommandText = StoredProcedures.GET_NEW_QUESTION_NO;
                com.Parameters.Add(new OracleParameter("iNextQuestionNo", OracleType.Number)).Direction = ParameterDirection.InputOutput;

                adp = new OracleDataAdapter(com);
                ds = new DataSet();
                adp.Fill(ds);

How to get its return value?

Upvotes: 0

Views: 1358

Answers (3)

priyanka.sarkar
priyanka.sarkar

Reputation: 26498

I guess the problem is here

                adp = new OracleDataAdapter(com);
                ds = new DataSet();
                adp.Fill(ds);

You want a scalar value and not an entire record set.. right? So instead try like this

//some code snippet

db.ExecuteNonQuery(cmd);               
iNextQuestionNo= (decimal?)cmd.Parameters[0].Value;

Hope this helps

Upvotes: 0

user177325
user177325

Reputation:

I wanted to add a comment/question to your reply there Paul, but I couldnt. Apologize for my ignorance, but if you are using a SQL Server stored procedured with isolation level serializable, arent supposed all the sql tables to be locked for the time the transaction/stored procedure last, giving no problems of concurrency? is this a bad practice?

Upvotes: 0

Michał Niklas
Michał Niklas

Reputation: 54292

Isn't it better to use function? Just like:

create function prc_GetNewQuestionNo(iNextQuestionNo IN NUMBER)
return number AS
    iQuestionNo NUMBER ;
BEGIN
    Select MAX(QUESTIONNO)+ 1 INTO iQuestionNo from tblIFFCOQUESTIONMASTER;
    return iQuestionNo;
END prc_GetNewQuestionNo;

Upvotes: 1

Related Questions