How to INSERT INTO [temp table] FROM [Stored Procedure] and Select * FROM [temp table]

I have something like

Create TABLE #members
(   
    [member_id] [bigint] NOT NULL,
    [registration_id] [int] NOT NULL,
    [date_modified] [datetime] NULL,
    [date_created] [datetime] NULL
)
INSERT #members
(
    [member_id],
    [registration_id],
    [date_modified],
    [date_created]
)
EXEC
dbo._roster_me开发者_高级运维mber_GetMemberContacts_byMember_id @Member_id = 1000
Select * from #members 

when I run it in query analyzer window: There is already an object named '#members' in the database.

In Query Analyzer, unless you explicitly DROP the temporary table, the temporary table will exist for the life of the Query Analyzer window.

Use the following idiom:

if object_id('tempdb..#temptable', 'U') is not null
    drop table #temptable
create table #temptable ( ... )
... rest of SQL ...

Obviously substituting the name of your temp table.