The memory process in SQL Server returns a record set very simple, but if you don't pay attention, it will be easily erroneous, you can't get the correct recordset. During the stored procedure written yesterday, the stored procedure was written correctly, but it always got the correct return record set, depressed for a long time. Later, I have also encountered the same problem before, actually tripped twice on the same issue and fainted. The reason is that in addition to the SELECT operation returns the record set in the stored procedure, there is no other operation (such as insert, delete, update), otherwise the recordset is not returned. I called another stored procedure (updated operation) during this store, remove the calling process, and returned the record set correctly. In the other two stored procedures, the print statement was used during the two stored procedures, and the recordset was not returned. After removing the Print statement, it got the correct recordset. If the stored procedure needs to return multiple record sets, get the next recordset with the RecordSet.NextRecordset method.
Sometimes, in the storage process is a splicing SQL statement, which is often used in the work, is the problem of row-to-line, requires splicing SQL statements. The SQL statement that performs splicing during the stored procedure needs to execute with exec sp_executesql @sql, where @SQL must be a variable of NVARCHAR type, and the length cannot exceed 4000. There is also a table variable in yesterday's work, then use the splicing SQL statement, actually said that the table variable is not defined, so depressed, forced to help, create a temporary table in the database. Later, I saw that when using sp_executesql to execute the SQL statement, if the SQL statement is a Table variable, the variable must be defined as global variable, \@ Table type rather than @Table type variable. However, the global variable is not good at the time of release.
