Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2006-07-04
Medium Priority
Last Modified: 2013-12-09
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.

Question by:IngvarNilsen
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 19

Expert Comment

ID: 17038889
are you using interbase or firebird? what version?

Author Comment

ID: 17038896
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)

LVL 19

Expert Comment

ID: 17040413
firebird 1.5 is fine for this, anything earlier doesn't have this. You want the context variable ROW_COUNT

return_param = ROW_COUNT;

Author Comment

ID: 17040787

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

LVL 19

Accepted Solution

NickUpson earned 1500 total points
ID: 17061369
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)"

Featured Post

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.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

650 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