Reading Output Values from Stored Procedure Form Action Types

When configuring a stored procedure form action type in FormsPro, it’s important to understand how FormsPro reads values returned from SQL Server.

SQL Server provides multiple ways to return values from a stored procedure, but FormsPro only supports one of them.

SQL Server stored procedures can return data using:

• OUTPUT parameters

• RETURN (integer status code)

• SELECT statements (result sets)

However, FormsPro only reads values returned through a SELECT statement. FormsPro does not capture values returned in OUTPUT parameters, nor RETURN statements.

To ensure FormsPro can read values correctly, your stored procedure must:

• Return values using a SELECT statement

• Return a single result set

• Return only one row

• Return one or more columns (multiple columns are supported)

For example:

CREATE PROCEDURE [dbo].[obsp_ReturnDateTimeValueInput] 
    @DateTimeValue DATETIMEOFFSET
AS
BEGIN
    SELECT @DateTimeValue AS [ValueToOutput]
END
GO

In this example:

• A single row is returned

• One column (ValueToOutput) is returned

• FormsPro can successfully read this value

A typical FormsPro scenario is:

1. A form collects user input

   • First Name

   • Last Name

   • Email

2. The stored procedure:

   • Inserts a new record into a table

   • Returns the new CustomerID (and optionally other values) back to FormsPro

Sample Table (for reference)

CREATE TABLE dbo.Customers
(
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(100),
    LastName NVARCHAR(100),
    Email NVARCHAR(255),
    CreatedDate DATETIMEOFFSET DEFAULT SYSDATETIMEOFFSET()
)
GO

FormsPro-Compatible Stored Procedure

CREATE PROCEDURE dbo.obsp_CreateCustomer
    @FirstName NVARCHAR(100),
    @LastName NVARCHAR(100),
    @Email NVARCHAR(255)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.Customers
    (
        FirstName,
        LastName,
        Email
    )
    VALUES
    (
        @FirstName,
        @LastName,
        @Email
    );

    -- Return the newly created CustomerID (and other values if desired)
    SELECT
        SCOPE_IDENTITY() AS CustomerID,
        @Email AS Email;
END
GO

Why This Works

• The procedure inserts a record.

• It returns values using a SELECT statement.

• It returns one row.

• It can return multiple columns.

• FormsPro can read and map these values back to the form.

When using the stored procedure form action type, always return data using a single row SELECT statement. If you follow this pattern, FormsPro will reliably read and process your returned values.