I have the following code. If I run it for a single date it takes about a minute to run, however when I set up the loop to run through just a few days, it runs for ages then fails.

Can anyone see what I am missing or why the loop doesn’t just take a minute for for each iteration.

Declare @pStartDate Varchar(10) = '2017-07-01'
Declare @pEndDate Varchar(10) 

While Convert(Date, @pStartDate) < '2017-07-04'
Begin
    
    set @pEndDate = @pStartDate
    
    Print @pStartDate
    Print @pEndDate

    Begin Transaction Loop1

    Insert Into 
        dbo.QueryOutput
    Select
        *   
    From 
        fnQuery(@pStartDate, @pEndDate) 

    Commit Transaction Loop1

    set @pStartDate = Convert(varchar(10), DateAdd(dd, 1, Convert(Date, @pStartDate)))

End;