Reporting progress during long running stored procedure

Posts   
 
    
hotmail
User
Posts: 47
Joined: 06-Feb-2013
# Posted on: 07-Dec-2017 03:19:28   

Hello, Using Sql Server 2016, llbl v5.3 latest, adapter.

What's the best way of displaying SP progress in front end UI while calling action procedure using adapter? I have attached a sample sql sp that prints the desired progress message. Any guidance would be much appreciated. Thanks


CREATE PROCEDURE AdoProcess_Test1
AS
BEGIN
    SET NOCOUNT ON;
            
    DECLARE @time VARCHAR(16)
    SET @time = CONVERT(VARCHAR(16),GETDATE(),114)
    RAISERROR( 'Completed 25%% At %s', 2, 1, @time) WITH NOWAIT
    WAITFOR DELAY '00:00:03'
    SET @time = CONVERT(VARCHAR(16),GETDATE(),114) you
    RAISERROR( 'Completed 50%% At %s', 2, 2, @time) WITH NOWAIT
    WAITFOR DELAY '00:00:03'
    SET @time = CONVERT(VARCHAR(16),GETDATE(),114)
    RAISERROR( 'Completed 75%% At %s', 1, 3, @time) WITH NOWAIT
    WAITFOR DELAY '00:00:03'
    SET @time = CONVERT(VARCHAR(16),GETDATE(),114)
    RAISERROR( 'Completed 100%% At %s', 1, 4, @time) WITH NOWAIT
END;

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 07-Dec-2017 17:10:41   

mmm would the following work?

The SP writes the progress to a table (temp or not) and then having another SP reading the progress from the table.

So from your code, you call the main SP, then use a loop and inside call the second SP to get the progress, wait for couple of seconds, and repeat the loop.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 07-Dec-2017 18:12:45   

Polling is indeed a good way to do this. You can also call from your proc a custom extended stored procedure which calls your app over the network through RPC/namedpipe/socket etc. and sends the progress. The latter is a lot of hoopla and complexity, the former is perhaps a bit less efficient. You can also do what windows etc. does: just use a timer and progress the progress bar based on the timer wink

Additionally you can chop up your proc and call the parts individually, so you can progress the progress bar over the fragments.

Frans Bouma | Lead developer LLBLGen Pro