Getting the result set from a stored procedure

[pacsinte]

Hi,

I need to get and be able to process the result set returned by a stored procedure in the calling stored procedure. How can I do that?
I don't want/am not allowed to use table valued functions.

Thanks,
Bobby

 

[iker3000]

CREATE TABLE #Result_table(
...
like your result set
)


INSERT INTO #Result_table EXEC your_stroed_procedure paramter1, paremter2, ...

Iker

 

[pacsinte]

Thanks, but create table automatically commits if I'm correct ( i'm not sure about temp tables).
One solution that i found was to use a cursor and leave it open (close it and deallocate in the calling stored procedure ).

Thanks again,
Bobby

 

[sneki]

Don't use stored procedure, use Multistatement table-valued functions or Inline table-valued functions(I think, you need Multistatement...). You can read about user-defined functions in BOL.

 

[pacsinte]

Thanks sneki,

as far as i know you still cannot call a stored/extended stored proc from a function.

Thanks again,
Bobby

 

[SQLSister]

Try inserting the resluts of the stored procedure into a temp table and then using the temp table within the calling stored procedure.