Getting Data from Stored Procedures

SaveToDB products enable data retrieval from stored procedures.

These products create controls that allow users to modify parameter values.

Developers can provide value lists for parameters. For more details, see Parameter Values.

SaveToDB products analyze stored procedure definitions when available, facilitating the saving of changes to loaded data.

Developers have multiple options for manually configuring data saving. Refer to Saving Data for more information.

Connection wizards display only procedures that select data.

However, there are instances where the products may not accurately detect the results of a procedure.

Two common scenarios are:

• The wizard displays a stored procedure that does not return results.
• The wizard fails to display a procedure that does return results.

In both cases, consult the xls.objects table.

Use the HIDDEN object type to hide a procedure and the PROCEDURE type to make it visible.

DBGate and ODataDB Specific Features

ODataDB exposes stored procedures that select data as FunctionImports, returning a collection of EntitySets.

If a stored procedure does not permit saving changes, it marks the EntitySet as read-only.

DBGate and ODataDB do not support paging with stored procedures and return the entire result set.

To implement paging within a procedure that returns a large recordset, you can add @top and @skip parameters.

SaveToDB and DBEdit will ignore these parameters and pass NULL.

Here’s a sample Microsoft SQL Server procedure:

CREATE PROCEDURE [s01].[usp_cashbook]
    @account nvarchar(50) = NULL,
    @item nvarchar(50) = NULL,
    @company nvarchar(50) = NULL,
    @top int = NULL,
    @skip int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        t.id,
        CAST(t.[date] AS datetime) AS [date],
        t.account,
        t.item,
        t.company,
        t.debit,
        t.credit
    FROM
        s01.cashbook t
    WHERE
        COALESCE(t.account, '') = COALESCE(@account, t.account, '')
        AND COALESCE(t.item, '') = COALESCE(@item, t.item, '')
        AND COALESCE(t.company, '') = COALESCE(@company, t.company, '')
    ORDER BY
        t.id
    OFFSET COALESCE(@skip, 0) ROWS FETCH NEXT COALESCE(@top, 1000000) ROWS ONLY;

END

Select Procedure Samples

Microsoft Excel has specific requirements for selecting data from stored procedures using OLEDB and ODBC drivers.

Refer to the working samples below if you encounter errors.

Also, use the suggested solutions to resolve parameter and table column names.

SQL Server | MySQL | PostgreSQL | Oracle | Snowflake

Select Procedure Sample for Microsoft SQL Server

Remember that SET NOCOUNT ON is required for SQL Server stored procedures to select data in Microsoft Excel.

CREATE PROCEDURE [s02].[usp_cashbook2]
    @account_id int = NULL,
    @item_id int = NULL,
    @company_id int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        t.id,
        CAST(t.[date] AS datetime) AS [date],
        t.account_id,
        t.item_id,
        t.company_id,
        t.debit,
        t.credit
    FROM
        s02.cashbook t
    WHERE
        COALESCE(@account_id, t.account_id, -1) = COALESCE(t.account_id, -1)
        AND COALESCE(@item_id, t.item_id, -1) = COALESCE(t.item_id, -1)
        AND COALESCE(@company_id, t.company_id, -1) = COALESCE(t.company_id, -1);
END

Select Procedure Sample for MySQL

CREATE PROCEDURE s02.usp_cashbook2 (
    account_id int,
    item_id int,
    company_id int
)
BEGIN
    SELECT
        *
    FROM
        s02.cashbook p
    WHERE
        COALESCE(account_id, p.account_id, -1) = COALESCE(p.account_id, -1)
        AND COALESCE(item_id, p.item_id, -1) = COALESCE(p.item_id, -1)
        AND COALESCE(company_id, p.company_id, -1) = COALESCE(p.company_id, -1);
END
//

Select Procedure Sample for PostgreSQL

CREATE OR REPLACE FUNCTION s02.usp_cashbook2 (
    account integer,
    item integer,
    company integer
)
RETURNS table (
    id integer,
    date date,
    account_id integer,
    item_id integer,
    company_id integer,
    debit double precision,
    credit double precision
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        p.id,
        p.date,
        p.account_id,
        p.item_id,
        p.company_id,
        p.debit,
        p.credit
    FROM
        s02.cashbook p
    WHERE
        COALESCE(account, p.account_id, -1) = COALESCE(p.account_id, -1)
        AND COALESCE(item, p.item_id, -1) = COALESCE(p.item_id, -1)
        AND COALESCE(company, p.company_id, -1) = COALESCE(p.company_id, -1);
END
$$;

Select Procedure Sample for Oracle Database

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2 (
    ACCOUNT_ID IN NUMBER,
    ITEM_ID IN NUMBER,
    COMPANY_ID IN NUMBER,
    DATA OUT SYS_REFCURSOR
)
AS
BEGIN
    OPEN DATA FOR
    SELECT
        p.ID,
        p."DATE",
        p.ACCOUNT_ID,
        p.ITEM_ID,
        p.COMPANY_ID,
        p.DEBIT,
        p.CREDIT
    FROM
        S02.CASHBOOK p
    WHERE
        COALESCE(USP_CASHBOOK2.ACCOUNT_ID, p.ACCOUNT_ID, -1) = COALESCE(p.ACCOUNT_ID, -1)
        AND COALESCE(USP_CASHBOOK2.ITEM_ID, p.ITEM_ID, -1) = COALESCE(p.ITEM_ID, -1)
        AND COALESCE(USP_CASHBOOK2.COMPANY_ID, p.COMPANY_ID, -1) = COALESCE(p.COMPANY_ID, -1);
END;
/

Select Procedure Sample for Snowflake

Snowflake supports stored procedures written in JavaScript, and the usage scenario remains the same.