I have used temp table in my stored procedure and unable to get the result when executing. It return result fine when running the same as query.

my query structure is something like...

ALTER PROCEDURE [dbo].[test]

as begin
SET NOCOUNT ON;

SELECT * INTO #Dates FROM Dates -- Used temp table

SET @Query = '
SELECT [Name], [HotelName], '+@Dates+'
FROM (  SELECT  , HD.[Name], HD.[HotelName], HD.Price
        FROM #Dates D
        LEFT JOIN #HotelData HD
        ON D.DateVal = HD.OccDate) T
PIVOT ( SUM(Price) FOR [Date] IN ('+@Dates+') ) AS PT'
execute(@Query)
end
Exec test -- Exectuting, I am getting message command completed successfully