Executing sql statement from within stored procedure

I have the following stored procedure. I tested sql statement by itself and it seems to work as intended. (The idea is that I need to collect average percentage for all the records within each hour (from 00:00 to 00:59, then from 01:00 to 01:50 etc. Starting from 00:00 to current time)
 But when I try to execute resulting sql statement from inside the stored procedure, I get "The command(s) completed successfully." instead of an intended result set that should be returned by my SELECT sql. Please help.

My code is below:

CREATE PROCEDURE dbo.GetData
AS
DECLARE @sqlStr AS VARCHAR(8000)
DECLARE @startDate DATETIME
DECLARE @finalDate DATETIME
SET @startDate = convert(datetime,convert(varchar(8),getdate(),112)+' 00:00:00.000')
SET @finalDate=GETDATE()


SET @sqlStr=@sqlStr +"SELECT RIGHT('" + CONVERT(varchar(20),@startDate) + "',8)  As [Hour],"
SET @sqlStr=@sqlStr +"AVG(View1.Percentage) AS AVERAGE_PERCENTAGE "
SET @sqlStr=@sqlStr +"FROM Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID) "
SET @sqlStr=@sqlStr +"WHERE ([View1.EventTime] BETWEEN '" + CONVERT(varchar(20),@startDate) + "' AND DATEADD(minute, 59,' " + CONVERT(varchar(20),@startDate) + "')) AND "  
SET @sqlStr=@sqlStr +"(Names.Name 'test%')  "


WHILE @startDate <= @finalDate
BEGIN

--increment
SET @startDate=DATEADD(hour, 1, @startDate)

SET @sqlStr=@sqlStr +" UNION SELECT RIGHT('" + CONVERT(varchar(20),@startDate) + "',8)  As [Hour],"
SET @sqlStr=@sqlStr +"AVG(View1.Percentage) AS AVERAGE_PERCENTAGE "
SET @sqlStr=@sqlStr +"FROM Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID) "
SET @sqlStr=@sqlStr +"WHERE ([View1.EventTime] BETWEEN '" + CONVERT(varchar(20),@startDate) + "' AND DATEADD(minute, 59,' " + CONVERT(varchar(20),@startDate) + "')) AND "  
SET @sqlStr=@sqlStr +"(Names.Name 'test%')  "

END
EXEC(@sqlStr)

GO
Start Free Trial