Ado calls the stored procedure to return multiple record sets (about the role of set nocount on in the Stored Procedure)

Recently, I answered several similar questions on csdn.

Http://community.csdn.net/Expert/TopicView.asp? Id = 4131244

Http://community.csdn.net/Expert/TopicView.asp? Id = 4156955

Generally, I asked why an error occurred while not returning a dataset or assigning a record set to the table control.

There are several reasons for the problem

1. It is unclear about the return mechanism of ADO. Connection. Execute. All records returned using this method are read-only and only forward cursor records.

Therefore, an error occurs when some table controls are assigned.

2. Set ncount on is not used in the stored procedure or improperly used.

Set nocount on is used to prevent SQL Server from returning the number of records affected by SQL statement operations,

Most SQL statements generate operation records, such as select, delete, update, insert ........

However, ADO treats the number of Operation Records returned by each operation as a record set. If more than one operation record is returned

The record set is returned.

However, ADO itself supports multiple record sets, and Rs. nextrecordset can be used to obtain any record set in a record set.

Therefore, you may obtain only the number of Operation records of a statement, rather than the record set to be returned.

Solution: place set nocount on at the beginning of the stored procedure

This is advantageous, so that SQL server does not affect the operation of each statement sent to the client, thus reducing network communication.