Getting Data from Stored Procedures

Getting Data from Stored Procedures

SaveToDB products allow getting data from stored procedures.

The products create controls to allow users to change parameter values.

Developers can supply value lists for parameters. See Parameter Values.

SaveToDB products analyze stored procedure definitions if available to allow saving changes of loaded data.

Developers can use several ways to configure saving changes manually. See Saving Data.

Connection wizards show only procedures that select data.

In several cases, products cannot detect the procedure results correctly.

There are two cases:

  • The wizard shows a stored procedure that does not return results.
  • The wizard does not show a procedure that returns results.

In both cases, use the xls.objects table.

Specify the HIDDEN object type to hide the procedure and the PROCEDURE type to show it.

DBGate and ODataDB Specific Features

ODataDB publishes stored procedures that select data as FunctionImports that return a collection of EntitySets.

If a stored procedure does not allow saving changes, it annotates the EntitySet read-only.

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

If a procedure returns a very large recordset, you can add @top and @skip parameters to implement paging within a procedure.

SaveToDB and DBEdit ignore such parameters passing NULL.

Here is 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 to select data from stored procedures using OLEDB and ODBC drivers.

Use the working samples below if you have an error.

Also, use the suggested solutions to resolve the names of parameters and table columns.

SQL Server | MySQL | PostgreSQL | Oracle | DB2 | NuoDB | Snowflake

Select Procedure Sample for Microsoft SQL Server

Note 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 IBM DB2

--#SET TERMINATOR %%

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2 (
    IN ACCOUNT_ID INTEGER
    , IN ITEM_ID INTEGER
    , IN COMPANY_ID INTEGER
    )
    DYNAMIC RESULT SETS 1
    READS SQL DATA
    DETERMINISTIC
    CALLED ON NULL INPUT
    COMMIT ON RETURN NO
    LANGUAGE SQL
P1: BEGIN

    DECLARE Cursor1 CURSOR WITH RETURN 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);

    OPEN Cursor1;

END P1
%%

--#SET TERMINATOR ;

Select Procedure Sample for NuoDB

CREATE PROCEDURE S02.USP_CASHBOOK2 (
    IN ACCOUNT_ID INTEGER
    , IN ITEM_ID INTEGER
    , IN COMPANY_ID INTEGER
    )
RETURNS tmp_tab (
    ID INTEGER,
    DATE DATETIME,
    ACCOUNT_ID INTEGER,
    ITEM_ID INTEGER,
    COMPANY_ID INTEGER,
    DEBIT DOUBLE,
    CREDIT DOUBLE
    )
AS
VAR ACCOUNT_ID1 INTEGER = ACCOUNT_ID;
VAR ITEM_ID1 INTEGER = ITEM_ID;
VAR COMPANY_ID1 INTEGER = COMPANY_ID;

INSERT INTO tmp_tab
SELECT
    p.ID
    , p.DATE
    , p.ACCOUNT_ID
    , p.ITEM_ID
    , p.COMPANY_ID
    , p.DEBIT
    , p.CREDIT
FROM
    S02.CASHBOOK p
WHERE
    COALESCE(p.ACCOUNT_ID, -1) = COALESCE(ACCOUNT_ID1, p.ACCOUNT_ID, -1)
    AND COALESCE(p.ITEM_ID, -1) = COALESCE(ITEM_ID1, p.ITEM_ID, -1)
    AND COALESCE(p.COMPANY_ID, -1) = COALESCE(COMPANY_ID1, p.COMPANY_ID, -1);

END_PROCEDURE
@@

Select Procedure Sample for Snowflake

Snowflake supports stored procedures written in JavaScript.

The usage scenario is the same.