How to retrieve results from a stored procedure?
Posted on 2003-10-22
I am trying to retrieve the results from a stored procedure in code. (Currently running jobs from sp_help_job)
I cannot use INSERT INTO... EXEC because the stored procedure itself uses an INSERT INTO... EXEC statement, and MS-SQL does not permit a nested INSERT INTO... EXEC.
I tried to return the EXEC sp_xxx from a function as RETURNS TABLE, but it is not possible because if I use an "Inline Table-valued Function" the only permitted statement is SELECT, and if I use a "Multi-statement Table-valued Function" that permits EXEC statement, I have to state a table name and definition and return that table.
Either retrieving results from a stored procedure, or having another way to get the currently running jobs, will be a good solution for me.
I really need to done this, please help me.