quote:

---

Originally posted by harlingtonthewizard

I have a stored procedure with two statement blocks. When I run this from server explorer stored procedures it returns two result sets as expected however if I use the stored procedure within a dataset for a reporting services layout the dataset only includes the fields for the first statement block. Any ideas?

ALTER PROCEDURE Total_Duration_Stored
-- Add the parameters for the stored procedure here
@SourceStartTime DateTime = NULL,
@SourceEndTime DateTime = NULL,
@SiteName nvarchar(50) = NULL

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT SUM(TotalSeconds) / 86400 AS Days, SUM(TotalSeconds) % 86400 / 3600 AS Hours, SUM(TotalSeconds) % 3600 / 60 AS Minutes, SUM(TotalSeconds)
% 60 AS Seconds, SiteName, TransmitterType, COUNT(EventID) AS EventCount
FROM (SELECT TimeDetails_1.TimeID, DATEDIFF(second, Duration.StartTime, Duration.EndTime) AS TotalSeconds, SiteDetails.SiteName,
SiteDetails.TransmitterType, EventDetails.EventID
FROM (SELECT TimeID,
CASE WHEN TimeDetails.SourceStartTime < @SourceStartTime THEN @SourceStartTime ELSE TimeDetails.SourceStartTime END
AS StartTime,
CASE WHEN TimeDetails.SourceEndTime > @SourceEndTime THEN @SourceEndTime ELSE TimeDetails.SourceEndTime END AS
EndTime
FROM TimeDetails) AS Duration INNER JOIN
TimeDetails AS TimeDetails_1 ON Duration.TimeID = TimeDetails_1.TimeID INNER JOIN
EventDetails ON TimeDetails_1.TimeID = EventDetails.TimeID INNER JOIN
SystemState ON EventDetails.EventID = SystemState.EventID INNER JOIN
SiteDetails ON SystemState.SiteID = SiteDetails.SiteID
WHERE (NOT (TimeDetails_1.SourceEndTime < @SourceStartTime)) AND (NOT (TimeDetails_1.SourceStartTime > @SourceEndTime)) AND
(SiteDetails.SiteName IN (@SiteName))) AS TotalSecondsTable
GROUP BY SiteName, TransmitterType
ORDER BY SiteName

SELECT @SourceStartTime AS UserStartTime, @SourceEndTime AS UserEndTime
END


Running [dbo].[Total_Duration_Stored] ( @SourceStartTime = 1/1/2006, @SourceEndTime = 12/12/2009, @SiteName = ben ).

Days Hours Minutes Seconds SiteName TransmitterType EventCount
----------- ----------- ----------- ----------- -------------------------------------------------- -------------------------------------------------- -----------
2 3 6 42 Ben FastTrace/FT-R/FastTx 5
No rows affected.
(1 row(s) returned)
UserStartTime UserEndTime
----------------------- -----------------------
1/01/2006 12/12/2009
No rows affected.
(1 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[Total_Duration_Stored].

---