Report Parameters (Example)

This simple example allows a stored procedure to display a dialogue with instructions and a prompt for missing parameters before proceeding.

The procedure checks its inputs, and if anything is missing, it exits and asks dbFront to prompt the user for the missing information. dbFront prompts the user and passes their response back to the procedure. The procedure can optionally trigger an additional prompt or run to completion..

NOTE: This example depends on functionality found in dbFront 1.4.1.

The Procedure

Below is the top of the procedure, but with the code sections delimited and explained.

1. Parameters

The procedure is created with one special parameter @dbfFormData that allows dbFront to pass the FormData from the previous request. For more details see: API Fields

The first section within the procedure does a quick check to ensure that the procedure is given JSON data as expected.

CREATE PROCEDURE [dbo].[spProcessData]

		(

		    @dbfFormData nvarchar(MAX) = null

		)

		AS

		BEGIN

		    -- SET NOCOUNT ON added to prevent extra result sets from

		    -- interfering with SELECT statements.

		    SET NOCOUNT ON;

		    -- Ensure that the parameters are valid JSON

		    IF (ISJSON(@dbfFormData,object) = 0) BEGIN

		        ;THROW 51000, 'The FormData JSON is invalid', 1;

		        return;

		    END;

2. Field and Button Constants

Using specific field and button constants may seem more verbose, but it enables field names and button prompts to be safely changed without breaking the procedure.

    DECLARE @BUTTON_CANCEL as varchar(32) = 'Cancel';

		    DECLARE @BUTTON_RUN as varchar(32) = 'Run';

		    DECLARE @FIELD_ReportYear as varchar(32) = 'Report Year';

3. Button Click - Cancel

If the user clicks 'Cancel', then simply exit.

    DECLARE @buttonClicked as varchar(32) = JSON_VALUE(@dbfFormData,'$.ButtonClicked');

		    IF @buttonClicked = @BUTTON_CANCEL BEGIN

		        select 'close' as action;

		        return; -- Cancelled Request.

		    END;

4. Validate Inputs and Prompt if Missing or Invalid

The procedure then parses the @dbfFormData looking for the required parameters. If anything is missing, it tells dbFront to display a dialogue to prompt for the missing data. See: OpenDialog

If all of the inputs are valid, then the procedure continues to the end.

DECLARE @YEAR as int = try_cast(JSON_VALUE(@dbfFormData,concat('$.FormData."', @FIELD_ReportYear, '"')) as int);

		DECLARE @minYear as int = (select min(r.Year) + 1 from Referrals r);

		DECLARE @maxYear as int = year(getdate());

		IF @YEAR is null OR not @YEAR between @minYear and @maxYear BEGIN        

		        SELECT

		            'OpenDialog' as Action

		            , concat(

		                '<h1>Report</h1>',

		                '<hr>',

		                '<h2>Please enter a report year (', @minYear, ' to ', @maxYear , ')</h2>') as Message

		            , concat('{"', @FIELD_ReportYear, '":"int" }') as Fields

		            , concat('{"', @BUTTON_CANCEL, '":null, "', @BUTTON_RUN,'":null }') as Buttons;

		        return; -- Early exit to draw the form.

		END;

		-- The rest of the stored procedure body follows here.

dbFront Configuration

Once the stored procedure is ready, there is only a small bit of dbFront configuration. All of the business logic and custom UI are defined in the stored procedure.

Create a new RunProcedure Action Button for the spProcessData stored procedure with the following settings:

Main Tab
- Caption: "Run Procedure"
- Action Type: Run Procedure
Procedure Tab
- Procedure: "spProcessData"
- Parameters: {dbfsp_data%}
- Output: Automatic

Benefits

The Procedure API allows you to add highly customized and complex business logic and user interactions to your database applications while leaving the technical aspect of web development to dbFront.