SQL Server Temporary Table from Complex Stored Procedure

I have to create a temp table (#temp) from a variable or stored procedure

(i.e)

My stored procedure contains

......

set @sql='select ...'
set @sql=@sql+'..join..'
set @sql=@sql+'....join..'

when i execute it (i.e) exec (@sql) it returns some rows,i want to store that rows in a temp table.How to acheieve it?

i tried something like

select
       id,name,ward_no into,........ 
#temp

开发者_StackOverflow中文版from
   ( 
     exec (@sql)
   )derived

Update

I found the logic

(i.e)

create table #temp(....)

insert #temp exec(@sql) 

select * from #temp

Not exactly what you asked for in your question, but this might be one way of doing what you're after anyway:

CREATE TABLE #temp (
ID INTEGER
)

DECLARE @Sql NVARCHAR(100)
SET @Sql = 'INSERT INTO #temp VALUES (5)'

EXEC(@Sql)

SELECT * FROM #temp

change #temp to ##temp and you'll be able to do that