What happens if we use Execute NonQuery with select Query?

Question

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

Akkiraju Ivaturi · 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.

Akkiraju Ivaturi · Answer

No Problem!!! If you have any other questions feel free to ask...

Dhara Maheshwari · Answer

Grt!! ur ans helped me it was a good piece of information!! Thanks

Santhosh Kumar Jayaraman · Answer

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.

Akshay Patel · Answer

Definition is perfect it returns no of rows. And in your case it returns -1 as you are trying use ExecuteNonQuery with select.