Dynamically Refresh Stored Procedure Results

Question

It would be very useful if the results of a stored procedure could automatically refresh themselves as a process completes on the database.

Two of many use cases are:

• You are monitoring an external process and monitoring a log table.
• You are monitoring one or more processes that are using the SQL Server Service Broker to execute Asyncronously.

Answer 1

As of dbFront 1.4.0.0517 stored procedures and direct dbFront to reload the request after a timed interval.

The result type on the RunProcedure button should be Automatic or ServerDirected. For more details see: Action Buttons

To trigger an automatic refresh the stored procedure should set the action to OpenDialog and specify the field ReloadDelay in the closing select.

select 'OpenDialog' as Action, 'This will cause a two second delay before refreshing.' as Message, 2 as ReloadDelay;

The following is an example is from the depreciation calculator. It creates a formatted message if there is still data to process and tells dbFront to reload in 2 seconds.

/* Write out the Message and Data */
DECLARE @headingHtml varchar(1024);
set @headingHtml = concat(
	'<h1>Calculating Depreciation for ',@CalendarYear ,'</h1>',
	'<p>Added $', cast(@DepreciationRateYearly as varchar), ' of ', @DepreciationMethodName, ' depreciation.</p>'
);

if exists (select * from [DepreciationEntry] d where d.AssetID = @AssetID and d.Year = @CalendarYear)
begin
	select 'OpenDialog' as Action, @headingHtml as Message, 2 as ReloadDelay;
end;
select * from [DepreciationEntry] d where d.AssetID = @AssetID and d.Year = @CalendarYear;