This may be a dumb question, I have tried googling but I am probably not phrasing this properly so I will try to explain the problem here:
I am using SQL Server 2000 and I have a stored procedure (SP1) that iterates through a table with 3 records and executes another stored procedure (SP2) at each iteration.
SP2 does an insert and then does a select on the inserted record.
SP1 continues after the iterating, it does one select then ends.
When I execute SP1 I only want the resultset from that last select returned. But with this iteration I am getting 4 resultsets (1 for each of the iterations and then the SP1 select at the end) all returned.
Is there some way to say don't return the resultset of SP2 when I am iterating them as part of SP1?
I could create another stored procedure that is exactly the same as SP2 but which does not have a select at the end, that would probably handle it. But I would prefer to add something to SP1 which prevents the resultset from returning. Is that possible?
Here's what the iteration in SP1 looks like right now:
set nocount on
declare c cursor for select room_id from st_room_ref where active = 1
declare @room_id int,
@max_c int
open c
while 1 = 1
begin
fetch c into @room_id
if @@fetch_status <> 0 break
select @max_c = max(call_id)
from st_call
where room_id = @room_id
if @max_c is null
exec st_getCall @room_id
end
close c
deallocate c
Please no dissertations on the wrongness of cursors. Just was hoping for some way to tell the st_getCall SP not to send it's result set back.
