What happens if we use Execute NonQuery with select Query?

Dhara Maheshwari

I have read many blogs and articles related to this but i didnt got the answer...
Definition says that, Execute Non Query returns only no. of rows affected and does not return colums or rows of table..

Interviewer always confuse us with this question..
Kindly give proper ans to it!!

Thanks in advance...

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Akkiraju Ivaturi14y ago
Accepted answer
If your stored procedure consists of "SET NO COUNT ON;" statement, it prevents the stored procedure to return the number of affected rows and it always makes the SP to return a value of -1. If you remove that statement, you will get the affected row count. If you write a stored procedure using Visual studio it automatically adds the statement. So the presence of the statement makes the difference for the return value.
0
Akkiraju Ivaturi14y ago
No Problem!!! If you have any other questions feel free to ask...
0
Dhara Maheshwari14y ago
Grt!! ur ans helped me it was a good piece of information!! Thanks
0
Santhosh Kumar Jayaraman14y ago
ExecuteNonQuery wil return number of rows affected by Insert, Update, Delete statements.

Even if you use select statment with ExecuteNonquery it will return -1.

Read this line from msdn and check the bolded line. Select statement will fall under other types of statement mentioned here.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1.
0
Akshay Patel14y ago
Definition is perfect, it returns no of rows. And in your case it returns -1 as you are trying use ExecuteNonQuery with select.
0