Clearing the pending results

[Steven Livingstone]

I have a stored procedure. I calls some other procedures and then returns a
single result.
However, the other stored procs are returning rows that i don't need.

How do I clear the results so that the other value returned is the one at
the end of my proc?

thanks,
steven

 

[William Ryan eMVP]

Steven:

First, how are you calling this, with a cmd.Executexxx method ? If so,
which one? If it's a datareader, you can use .NextResult to advance to the
next resultset. So if the thing you want it the fourth query, call
..NextResult three times, then your while dr.Read(0 will reference the last
query.

If you have multiple queries and you are using a dataset, then it's probably
filling multiple tables, and as such you'd reference the 4th table in the
dataset.

Also, i'm kind of lost on the part about "returns a single result" but then
you mention you don't want the stuff before this result. Not sure what's
happening there. Let me know the answers to the above, or ideally, post the
calling code snippet (just the execute stuff or the .Fill stuff) and the
proc definitions...I'd mainly need to know about what the procs are
returning.

Let me know and I'll see what I can do.

Cheers,

Bill

 

[Steven Livingstone]

Thanks William.

The scenario is this (and maybe less related to ado.net that it should be
for this forum)... in Query Analyser i can call an SP which basically calls
a number of other SP's and returns a result. The problem is that the SP's
that it calls all finish with a SELECT to return the identity of the newly
inserted row.

Obviously now each of these identities are returned even to the SP doing the
wrapping. Example below.

EXEC SP_Main

this returns...

123
243
343
564
434
11 <- this is the one i am after!

Inside SP_Main, i have something like...
------------------
EXEC SP_INSERT1 @somevalue
EXEC SP_INSERT2 @somevalue
EXEC SP_INSERT3 @somevalue
EXEC SP_INSERT4 @somevalue

--finally
insert into blah(somecolumn) values (@somevalue)

select @@Identity
------------------

The problem is that i only want "select @@Identity" to be shows in my
resultset so ideally *before* "select @@Identity", i could do something like
"SP_ClearResults", which would basically prevent anything returned from each
of the preceding EXEC statements from being shows in the selected resultset.

Any ideas? I'd rather not use ADO nextresult, but it is an option. I'd
rather do the "clearing" in the SP.

Make any more sense?

thanks,
Steven.