How to have a stored procedure in Interbase return a result when invoking ExecuteNonQuery in ASP.Net

When using ASP.Net components and using a Command's method ExecuteNonQuery, with an SQL string like
INSERT INTO bla bla I get an integer as a result, this usually is the number of records affected.

All is fine with this.
Now - I want to put this INSERT etc. into a stored procedure. And I have successfully done so, all works.
The problem is that now I get 0 - a zero as a result, when invoking ExecuteNonQuery.

So - what kind of syntax to use in the procedure to have it return an integer of my desire?
the RETURNS(bla bla) is not the answer (or probably not) I am after, please don't confuse this with my question, because I already know how to retrieve that kind of return values.

Who is Participating?
Nick UpsonConnect With a Mentor Principal Operations EngineerCommented:
what you can do is define your SP as returning a value, say rcount, set rcount = ROW_COUNT inside the SP
ensure the SP then has a SUSPEND and then you can call it as "select rcount from mysp (val1, val2)"
Nick UpsonPrincipal Operations EngineerCommented:
are you using interbase or firebird? what version?
IngvarNilsenAuthor Commented:
I am using Firebird
But I am not tied to it, in case a newer stable build is available, I might upgrade.
(This is a new project)

Nick UpsonPrincipal Operations EngineerCommented:
firebird 1.5 is fine for this, anything earlier doesn't have this. You want the context variable ROW_COUNT

return_param = ROW_COUNT;
IngvarNilsenAuthor Commented:

Ok, I am almost there.
To get the result returned from the ADO.Net components, I need to do it this way:

For "INSERT INTO bla bla" I must use ExecuteNonQuery
For "EXECUTE PROCEDURE FOO_INSERT bla bla" I must first use your solution and then use ExecuteScalar

What I wanted was to still use ExecuteNonQuery with the stored procedure, but this always returns 0
So, when there is a context variable like ROW_COUNT, could there be a context variable for the returning result?
Since "INSERT INTO.." do return a usable result, why can't a stored procedure do the same?

If you did not understand this, please tell me and I will try to explain.
And please note - I am not absolutely sure about the differences between ExecuteScalar and ExecuteNonQuery.
And BTW, finally. ExecuteScalar does NOT return the rows affected when using it with "INSERT INTO.." :-)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.