sql - From CURSOR to SELECT.... with an EXEC -

this example of sql query:

declare cursora cursor select ida ...  open cursora  fetch next cursora @my_id  while @@fetch_status <> -1 begin   if @@fetch_status <> -2   begin      insert #temp_table      exec sp_mystoredprocedure @my_id   end   fetch next cursora @my_id end  close      cursora  deallocate cursora  

how can transform select, example this:

insert #temp_table exec sp_mystoredprocedure ida  ... 

?

you can't directly query result set of stored procedure i'm afraid. presumably that's why cursor there in first place.

using cursors thought inefficient, replace this:

-- ids select ida #tmpids  -- add row numbers , index query speed (may not needed if small) alter table #tmpids add rownum int identity(1,1) create clustered index idx_tmpids_rownum on #tmpids(rownum)  declare @my_id int, @rownum int, @rowcount int    set @rownum = 1 select @rowcount = count(*) #tmpids  -- iterate on #tmpids while @rownum <= @rowcount begin     select @my_id = ida #tmpids rownum = @rownum      insert #temp_table     exec sp_mystoredprocedure @my_id      set @rownum = @rownum + 1 end