I've got a T-SQL stored procedure, which for a given set of parameters returns 34 rows and takes less then a fraction of a second to run.
The SQL has a while loop, a bunch of nested if statements a handful of modes. It used to have a few goto: statements, but I changed them to If statements.
All appropriate access (select to tables, exec to procedures) has been granted.
Whenever I try to add the SQL as a stored procedure to SSRS to build a report to pull the contents that are returned from the procedure the SSRS hangs. Just hangs. Wheel is spinning and it seems to be taking for ever.
I did run a test, and I am able to connect to the server, I was able to add a different procedure without issue in seconds. But something about this one keeps making it crash.
First a few labels are declared, mostly dates.
Then a few local tables are created.
Then we have a while loop that reads while 1=1, and I wonder if that could be causing some kind of infinite loop in SSRS.
Then syntax reads if a date value > another date value break (out of the loop)
else insert and update a bunch of stuff into the temp tables.
It does have syntax that starts
Here is a snippet of the code:
Parameters
@season_str varchar(255),
@event_start_dt datetime,
@event_end_dt datetime,
@sale_end_dt datetime)
------------------
declare @sale_start_dt datetime
select @sale_start_dt = dateadd(dd,-6,@sale_end_dt)
declare @run_date datetime,
@include_donated char(1)
--need to report on sales the day before the start date of the report so that we
-- can know how many were sold on day 1 of the report.
select @run_date = dateadd(dd,-1,@sale_start_dt)
select @include_donated = 'N'
create table #t1 (
perf_no int NOT NULL,
...
)
while 1=1
begin
select @run_date = dateadd(dd,1,@run_date)
if convert(datetime,convert(varchar,@run_date,101)) > convert(datetime,convert(varchar,@sale_end_dt,101))
BREAK
else
begin
if convert(varchar,@run_date,101) = convert(varchar,getdate(),101)
--if the run_date is today then we want to get up-to-date numbers so we run the procc with @run_date = NULL
begin --run_date = NULL
insert into #t1 ( field names )
exec LP_SALES_SUMMARY_PTC @event_start_dt, @event_end_dt, @season_str, NULL, @include_donated
update #t1 set run_date = @run_date, step = 1 where run_date is null
end --run_date is not null
else
begin --run_date is not NULL
--if the run date is in the past, run the procedure with @run_date <> NULL
insert into #t1 ( field names )
exec LP_SALES_SUMMARY_PTC @event_start_dt, @event_end_dt, @season_str, NULL, @include_donated
update #t1 set run_date = @run_date, step = 1 where run_date is null
if (select count(*) from #t1 where run_date = @run_date) = 0
begin
if getdate() >='08/29/2007' -- start of historic information
begin
insert into #t1 ( field names )
exec LP_SALES_SUMMARY_PTC @event_start_dt, @event_end_dt, @season_str, NULL, @include_donated
update #t1 set run_date = @run_date, step = 1 where run_date is null
end
else
begin
insert into #t1 ( field names )
exec RP_SALES_SUMMARY_PTC @event_start_dt, @event_end_dt, @season_str, NULL, @include_donated
update #t1 set run_date = @run_date, step = 1 where run_date is null
end
end
end
end
-- more code continuation
I'm not sure what's different about the calls to the stored procedure, but instead of the WHILE 1=1 pattern (which is notorious for causing infinite loops), I'd much rather make the condition based on the values. Also instead of subtracting a day from the start and then adding a day immediately inside the loop, it is simpler to leave the start day as is, and not increment it until the end of the loop.
I left out the creation and update of #t1, but here is how I would re-structure your variables and loop:
/* params */
DECLARE @season_str varchar(255),
@event_start_dt date,
@event_end_dt date,
@sale_end_dt date;
/* local variables */
declare @sale_start_dt date = dateadd(DAY, -6, @sale_end_dt);
declare @run_date date = @sale_start_dt,
@today date = getdate(),
@beginningOfTime date = '20070829', -- start of historic info
@include_donated char(1) = 'N';
...
WHILE @run_date <= @sale_end_dt
BEGIN
if @run_date = @today
begin
--insert into #t1 (...) EXEC LP_SALES_SUMMARY_PTC with null run_date
end
else
begin
--insert into #t1 (...) EXEC LP_SALES_SUMMARY_PTC with non-null run_date
end
if NOT EXISTS (select 1 from #t1 where run_date = @run_date)
begin
if @today >= @beginningOfTime -- start of historic information
begin
--insert into #t1 (...) EXEC LP_SALES_SUMMARY_PTC ... -- should it be RP?
end
else
begin -- how could this ever be reached?
--insert into #t1 (...) EXEC RP_SALES_SUMMARY_PTC ...;
end
end
set @run_date = dateadd(DAY, 1, @run_date);
END