Querying results of a stored procedure

Question

I have a stored procedure that returns a large number of results, and would like a better way to debug/ parse the results than copy/pasting into excel or whatever - is there a way to pass the results of the procedure into a query? e.g., if the procedure call was something like:

exec database..proc 'arg1','arg2','arg3'

my thought was to do something like:

select distinct column1 from 
(exec database..proc 'arg1','arg2','arg3')

which clearly did not work, or I wouldn't be here. If it matters, this is for a sybase database.

Thanks!

Answer 1

The code below works in MS SQL 2005. I don't have a Sybase installation right now to test it on that. If it works in Sybase you could use a temporary table (or permanent table) outside of your stored procedure so that you don't have alter the code that you're trying to test (not a very good testing procedure generally.)

CREATE TABLE dbo.Test_Proc_Results_To_Table
(
    my_id       INT         NOT NULL,
    my_string   VARCHAR(20) NOT NULL
)
GO

CREATE PROCEDURE dbo.Test_Proc_Results_To_Table_Proc
AS
BEGIN
    SELECT
        1 AS my_id,
        'one' AS my_string
END
GO

INSERT INTO dbo.Test_Proc_Results_To_Table (my_id, my_string)
EXEC dbo.Test_Proc_Results_To_Table_Proc
GO

SELECT * FROM dbo.Test_Proc_Results_To_Table
GO