How to Save Data Using Stored Procedures in SaveToDB

You can use stored procedures to save data changes to a database.

There are two distinct scenarios:

1. The select object has a fixed column set.
2. The select object has a dynamic column set.

In the first scenario, stored procedures can have a fixed set of parameters. In the second scenario, they cannot.

This section focuses on the first scenario. For the second scenario, see Saving Data Using JSON.

Configuration

To save changes using stored procedures, define procedures for INSERT, UPDATE, and DELETE operations.

Here’s a sample configuration for the xls.objects table:

Here’s a sample configuration for the QueryList view:

The SaveToDB products automatically create these configurations, linking procedures by the _insert, _update, and _delete suffixes.

For example, the row for the usp_cashbook2 procedure is optional.

Implementation Details

SaveToDB and DBEdit execute the specified stored procedures for insert, update, and delete operations directly.

DBGate executes the procedures on the server side based on POST, PUT, and DELETE commands.

ODataDB creates EntitySets for views and FunctionImports, along with new EntitySets for stored procedures and SQL codes.

Stored Procedure Parameters

Stored procedures can use parameters populated with values according to these rules:

1. Values from data columns with matching names, such as @id and @name, when the selected data includes the id and name columns.
2. Values from data query parameters with matching names, such as @account_id, when the select query includes the @account_id parameter.
3. Values from Excel named cells, such as @customer_id, for the named cell customer_id (only applicable for the SaveToDB Add-In).
4. Special context values like @rownum or @transaction_id.

SaveToDB 10+, DBEdit, DBGate, and ODataDB also support parameters with the source_ prefix, which retrieve source values of the loaded data before changes.

In some cases, data column names may not be suitable for parameter names, such as when they contain spaces (e.g., "customer name").

In such cases, replace prohibited characters with their XML-encoded forms. For example, use "customer_x0020_name".

Refer to Parameter Name Conventions for more details.

Also, see Context Parameters.

Sample Stored Procedures

Below are sample stored procedures for each supported database platform. Use these examples to select data and resolve parameter names and updated table columns.

SQL Server | MySQL | PostgreSQL | Oracle | Snowflake

Sample Stored Procedures for Microsoft SQL Server

Note that SET NOCOUNT ON is required in 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
GO

CREATE PROCEDURE [s02].[usp_cashbook2_insert]
    @date date = NULL,
    @account_id int = NULL,
    @item_id int = NULL,
    @company_id int = NULL,
    @debit money = NULL,
    @credit money = NULL
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO s02.cashbook
        ([date], account_id, item_id, company_id, debit, credit)
    VALUES
        (@date, @account_id, @item_id, @company_id, @debit, @credit);
END
GO

CREATE PROCEDURE [s02].[usp_cashbook2_update]
    @id int = NULL,
    @date date = NULL,
    @account_id int = NULL,
    @item_id int = NULL,
    @company_id int = NULL,
    @debit money = NULL,
    @credit money = NULL
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE s02.cashbook
    SET
        [date] = @date,
        account_id = @account_id,
        item_id = @item_id,
        company_id = @company_id,
        debit = @debit,
        credit = @credit
    WHERE
        id = @id;
END
GO

CREATE PROCEDURE [s02].[usp_cashbook2_delete]
    @id int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM s02.cashbook WHERE id = @id;
END
GO

Sample Stored Procedures 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
//

CREATE PROCEDURE s02.usp_cashbook2_insert (
    date date,
    account_id int,
    item_id int,
    company_id int,
    debit double,
    credit double
)
BEGIN
    INSERT INTO s02.cashbook
        (date, account_id, item_id, company_id, debit, credit)
    VALUES
        (date, account_id, item_id, company_id, debit, credit);
END
//

CREATE PROCEDURE s02.usp_cashbook2_update (
    id int,
    date date,
    account_id int,
    item_id int,
    company_id int,
    debit double,
    credit double
)
BEGIN
    UPDATE s02.cashbook t
    SET
        t.date = date,
        t.account_id = account_id,
        t.item_id = item_id,
        t.company_id = company_id,
        t.debit = debit,
        t.credit = credit
    WHERE
        t.id = id;
END
//

CREATE PROCEDURE s02.usp_cashbook2_delete (
    id int
)
BEGIN
    DELETE FROM s02.cashbook WHERE s02.cashbook.id = id;
END
//

Sample Stored Procedures 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
$$;

CREATE OR REPLACE FUNCTION s02.usp_cashbook2_insert (
    date date,
    account_id integer,
    company_id integer,
    item_id integer,
    debit double precision,
    credit double precision
)
    RETURNS void
    LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO s02.cashbook
        (date, account_id, company_id, item_id, debit, credit)
    VALUES
        (date, account_id, company_id, item_id, debit, credit);
END
$$;

CREATE OR REPLACE FUNCTION s02.usp_cashbook2_update (
    id integer,
    date date,
    account_id integer,
    company_id integer,
    item_id integer,
    debit double precision,
    credit double precision
)
    RETURNS void
    LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE s02.cashbook p
    SET
        date = usp_cashbook2_update.date,
        account_id = usp_cashbook2_update.account_id,
        company_id = usp_cashbook2_update.company_id,
        item_id = usp_cashbook2_update.item_id,
        debit = usp_cashbook2_update.debit,
        credit = usp_cashbook2_update.credit
    WHERE
        p.id = usp_cashbook2_update.id;
END
$$;

CREATE OR REPLACE FUNCTION s02.usp_cashbook2_delete (
    id integer
)
    RETURNS void
    LANGUAGE plpgsql
AS $$
BEGIN
    DELETE FROM s02.cashbook p WHERE p.id = usp_cashbook2_delete.id;
END
$$;

Sample Stored Procedures 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;
/

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2_INSERT (
    ID INTEGER,
    "DATE" DATE,
    ACCOUNT_ID INTEGER,
    ITEM_ID INTEGER,
    COMPANY_ID INTEGER,
    DEBIT DOUBLE PRECISION,
    CREDIT DOUBLE PRECISION
)
AS
BEGIN
    INSERT INTO S02.CASHBOOK
        ("DATE", ACCOUNT_ID, COMPANY_ID, ITEM_ID, DEBIT, CREDIT)
    VALUES
        ("DATE", ACCOUNT_ID, COMPANY_ID, ITEM_ID, DEBIT, CREDIT);
END;
/

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2_UPDATE (
    ID INTEGER,
    "DATE" DATE,
    ACCOUNT_ID INTEGER,
    ITEM_ID INTEGER,
    COMPANY_ID INTEGER,
    DEBIT DOUBLE PRECISION,
    CREDIT DOUBLE PRECISION
)
AS
BEGIN
    UPDATE S02.CASHBOOK P
    SET
        "DATE" = USP_CASHBOOK2_UPDATE."DATE",
        ACCOUNT_ID = USP_CASHBOOK2_UPDATE.ACCOUNT_ID,
        COMPANY_ID = USP_CASHBOOK2_UPDATE.COMPANY_ID,
        ITEM_ID = USP_CASHBOOK2_UPDATE.ITEM_ID,
        DEBIT = USP_CASHBOOK2_UPDATE.DEBIT,
        CREDIT = USP_CASHBOOK2_UPDATE.CREDIT
    WHERE
        P.ID = USP_CASHBOOK2_UPDATE.ID;
END;
/

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2_DELETE (
    ID INTEGER
)
AS
BEGIN
    DELETE FROM S02.CASHBOOK WHERE ID = USP_CASHBOOK2_DELETE.ID;
END;
/

Sample Stored Procedures for Snowflake

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