Saving Data Using Stored Procedures

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

There are two very different cases:

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

In the first case, stored procedures can have a fixed set of parameters. In the second case, they are not.

This topic discusses the first case. See Saving Data Using JSON for the second case.

Configuration

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

Here is a sample for the xls.objects table:

Here is a sample for the QueryList view:

SaveToDB products create such configurations automatically, 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 themselves.

DBGate executes the procedures on the server-side according to POST, PUT, and DELETE commands.

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

Stored Procedure Parameters

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

1. values from data columns with the same name like @id and @name when the selected data has the id and name columns;
2. values from data query parameters with the same name like @account_id when the select query has the @account_id parameter;
3. values from Excel named cells like @customer_id for named cell customer_id (the SaveToDB Add-In only);
4. special context values like @rownum or @transaction_id.

SaveToDB 10+, DBEdit, DBGate, and ODataDB also allow using parameters with the source_ prefix that get source values of the loaded data before changes.

In some cases, data columns can have names that are not suitable for parameter names, like space in the "customer name" column name.

In this case, you can replace prohibited characters with the XML-encoded form. For, example "customer_x0020_name".

See Parameter Name Conventions for details.

See also Context Parameters.

Sample Stored Procedures

Below you will find sample stored procedures for each supported database platform.

Use the suggested solutions to select data and resolve names of parameters and updated table columns.

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

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

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2_INSERT (
    ID INTEGER,
    DATE DATE,
    ACCOUNT_ID INTEGER,
    ITEM_ID INTEGER,
    COMPANY_ID INTEGER,
    DEBIT DOUBLE,
    CREDIT DOUBLE
    )
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    COMMIT ON RETURN YES
    LANGUAGE SQL
P1: 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 P1
%%

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2_UPDATE (
    ID INTEGER,
    DATE DATE,
    ACCOUNT_ID INTEGER,
    ITEM_ID INTEGER,
    COMPANY_ID INTEGER,
    DEBIT DOUBLE,
    CREDIT DOUBLE
    )
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    COMMIT ON RETURN YES
    LANGUAGE SQL
P1: 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 P1
%%

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2_DELETE (
    ID INTEGER
    )
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    COMMIT ON RETURN YES
    LANGUAGE SQL
P1: BEGIN

DELETE FROM S02.CASHBOOK P WHERE P.ID = USP_CASHBOOK2_DELETE.ID;

END P1
%%

--#SET TERMINATOR ;

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

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2_INSERT (
    ID INTEGER,
    DATE DATE,
    ACCOUNT_ID INTEGER,
    ITEM_ID INTEGER,
    COMPANY_ID INTEGER,
    DEBIT DOUBLE,
    CREDIT DOUBLE
    )
AS

INSERT INTO S02.CASHBOOK
    (DATE, ACCOUNT_ID, COMPANY_ID, ITEM_ID, DEBIT, CREDIT)
VALUES
    (DATE, ACCOUNT_ID, COMPANY_ID, ITEM_ID, DEBIT, CREDIT);

END_PROCEDURE
@@

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2_UPDATE (
    ID INTEGER,
    DATE DATE,
    ACCOUNT_ID INTEGER,
    ITEM_ID INTEGER,
    COMPANY_ID INTEGER,
    DEBIT DOUBLE,
    CREDIT DOUBLE
    )
AS

VAR ID1 INTEGER = ID;
VAR DATE1 DATE = DATE;
VAR ACCOUNT_ID1 INTEGER = ACCOUNT_ID;
VAR ITEM_ID1 INTEGER = ITEM_ID;
VAR COMPANY_ID1 INTEGER = COMPANY_ID;
VAR DEBIT1 DOUBLE = DEBIT;
VAR CREDIT1 DOUBLE = CREDIT;

UPDATE S02.CASHBOOK P
SET
    DATE = DATE1
    , ACCOUNT_ID = ACCOUNT_ID1
    , COMPANY_ID = COMPANY_ID1
    , ITEM_ID = ITEM_ID1
    , DEBIT = DEBIT1
    , CREDIT = CREDIT1
WHERE
    ID = ID1;

END_PROCEDURE
@@

CREATE OR REPLACE PROCEDURE S02.USP_CASHBOOK2_DELETE (
    ID INTEGER
    )
AS

VAR ID1 INTEGER = ID;

DELETE FROM S02.CASHBOOK WHERE ID = ID1;

END_PROCEDURE
@@

Sample Stored Procedures for Snowflake

Snowflake supports stored procedures written in JavaScript.

The usage scenario is the same.