SQL Server stored procedure causes Visual Studio to hang when added as part of a dataset

Question

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 

Answer 1

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