Can I clear the multiple result sets that a stored procedure has already gathered so that the next result set that is created becomes the very first result set returned by the procedure?
+2
A:
this would depend on the Database. In Sql Server, once the result set is sent, it is gone. The receiving application/code must deal with it. If you need to have logic like this, gather the results into a temp table and only return what is needed at the end of the procedure.
KM
2010-02-25 14:38:49
I'm still within the proc at the time I decide I want to cancel the current set and return a different set. So nothing has yet gone to the receiver.
DEH
2010-02-25 14:46:55
like I said in my answer, gather any results that are subject to "recall" in temp tables. when all of your logic is determined on what should be returned, you can SELECT from whatever temp tables you need to.
KM
2010-02-25 15:24:22
A:
As KM said it depends a bit on the database. Can you explain how your stored procedure gather multiple result sets? Are you achieving this via multiple unions or by creating a dynamic sql statement ?
MS SQL 2005. I have a proc that runs several entirely separate sql statements, each one of which will add a resultset to the multiple result sets output by the proc. At some point towards the end of the proc I want to 'cancel' the resultsets that have been created so far, and create a single new resultset which will be the only resultset then output by the proc and received by the client.
DEH
2010-02-25 15:21:12