Hi,
I use a table result from a SQL Server 2012 procedure for my report data.
The procedure works fine when tested in SSMS - however when I use it in my report I get
500 - Internal server error.
There is a problem with the resource you are looking for, and it cannot be displayed.
If I comment the inner nested cursor the report works, but obviously without the required data. I have included the proc below.
Why use cursors? will probably be asked - I used SQL PIVOT previously and it worked but takes 2 minutes to complete. This proc completes in 2 seconds. I don't know DBR well enough yet to use cross tabs.
Thanks for any suggestions.
USE [SMSC]
GO
/****** Object: StoredProcedure [dbo].[[ReportStatsByDay]] Script Date: 2014/06/05 06:24:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO -- [ReportStatsByDay] 4, '5 may 2014', '5 june 2014' ALTER PROCEDURE [dbo].[ReportStatsByDay]
@CustomerID int,
@Start datetime,
@End datetime
AS
BEGIN Declare @inLogin varchar(50)
Declare @CustomerName varchar(50)
Declare @CreditLimit int DECLARE @TempTime datetime
Declare @DateList Table ([Date] datetime, [Enroute] int, [Delivered] int, [Expired] int, [Rejected] int, [Undeliverable] int, [Unknown] int, [Balance] int)
Declare @Day datetime
Declare @ResultCode int
Declare @ResultCount int set @TempTime = @Start; WHILE @TempTime <= @End
BEGIN
INSERT @DateList values(@TempTime, 0, 0, 0, 0, 0, 0,0)
set @TempTime = DATEADD(day, 1, @TempTime)
END; Declare @DayCursor Cursor;
Declare @DayStatus int;
Declare @ResCursor Cursor;
Declare @ResStatus int;
Declare @Balance int; set @DayCursor = Cursor for select [Date] from @DateList order by [Date];
Open @DayCursor;
fetch next from @DayCursor into @Day;
set @DayStatus = @@FETCH_STATUS
While @DayStatus = 0
Begin
if @Day < '30 May 2014'
set @ResCursor = Cursor for
select ResultCode, Count(*) from
(
select MessageID, max(resultcode) ResultCode from
(
select r.MessageID, r.ResultCode
from SMSC.dbo.messagequeueArchive q left outer join SMSC.dbo.messageresultArchive r on q.MessageID=r.MessageID
where q.timeinserted between @Day and DATEADD(day, 1, @Day) and CustomerID=@CustomerID
) a group by MessageID
) b group by ResultCode
else
set @ResCursor = Cursor for
select ResultCode, Count(*) from
(
select MessageID, max(resultcode) ResultCode from
(
select r.MessageID, r.ResultCode
from SMSC.dbo.messagequeueArchive q left outer join SMSC.dbo.messageresultArchive r on q.ExternalRef=r.MessageID
where q.timeinserted between @Day and DATEADD(day, 1, @Day) and CustomerID=@CustomerID
) a group by MessageID
) b group by ResultCode set @Balance=(select top 1 Balance from SMSC.dbo.CustomerBalanceLog where CustomerID=@CustomerID
and abs(datediff(day,CustomerBalanceLog.balanceTime,@Day))<1 order by BalanceTime desc) update @DateList set Balance=@Balance where [Date]=@Day
--Open @ResCursor;
--fetch next from @ResCursor into @ResultCode, @ResultCount;
--set @ResStatus = @@FETCH_STATUS
--While @ResStatus = 0
--Begin
-- update @DateList set
-- Unknown = (case when @ResultCode is null then isnull(@ResultCount,0) else unknown end),
-- Enroute = (case when @ResultCode = 1 then isnull(@ResultCount,0) else Enroute end),
-- Delivered = (case when @ResultCode = 2 then isnull(@ResultCount,0) else Delivered end),
-- Expired = (case when @ResultCode = 3 then isnull(@ResultCount,0) else Expired end),
-- Undeliverable = (case when @ResultCode = 5 then isnull(@ResultCount,0) else Undeliverable end),
-- Rejected = (case when @ResultCode = 8 then isnull(@ResultCount,0) else Rejected end)
-- where [Date]= @Day -- fetch next from @ResCursor into @ResultCode, @ResultCount;
-- set @ResStatus = @@FETCH_STATUS
--End
--Close @ResCursor
fetch next from @DayCursor into @Day;
set @DayStatus = @@FETCH_STATUS End
Close @DayCursor
select * from @DateList END
