Sproc/Report Issues

[ChopinFan]

I'm trying to use a sproc for a report, but it isn't working. The procedure runs, but gives me a message "The stored procedure executed successfully but did not return records" and, therefore, when the report is based on it, it gives me a message saying "Provider command for child rowset does not produce a rowset". What am I doing wrong? This is a very simplified version of the sproc:

CREATE PROCEDURE dbo.MyProcedure
AS
declare @SQL as varchar (100)

select * into #temp1
from dbo.MyView

set @SQL = '
SELECT *
FROM #temp1'

exec(@SQL)

drop table #temp1