I have a stored procedure which needs to execute other stored procedures in order to generate its results. Unfortunately, those nested stored procedures can perform SELECTs. The end result is that, instead of returning one record set, my stored procedure may return multiple record sets.
How to prevent this? According to http://databases.aspfaq.com/database/using-stored-procedures.html:
After the BEGIN command, we SET NOCOUNT ON -- this prevents interim statements from being returned as recordsets.
Does NOCOUNT really affect whether or not multiple record sets are returned? Not when you're dealing with nested execution of stored procedures...
The only solution I can find is to create a temporary table, in the calling stored procedure, and to dump the results of the called stored procedure into that temporary table, then to delete it.
CREATE TABLE [#unused] (foo REAL, bar REAL); INSERT INTO [#unused] EXECUTE my_nested_sp @arg1, @arg2; DROP TABLE [#unused];