Deploy Stored Procedures in SAP Business One SQL Server

Stored procedures are units of business logic that are stored and used in a database. They can be used to add additional constraints or automatically generate additional data during database transactions. Square Bridge uses stored procedures with SAP Business One SQL Server to maintain the uniqueness of data created in SAP Business One from syncing with Square.

This topic describes how to add stored procedures to your SQL database and provides the business logic as source code that you store in your database for Square Bridge support.

Link to section

Best practices

The stored procedure code is intended to prevent duplicates on the SqReferenceId for any object including:

  • A/R invoices.
  • A/R credit memos.
  • Sales orders.
  • A/R downpayment invoices.
  • Incoming payments.
  • Outgoing payments.

In addition, the stored procedure should prevent duplicates on SqCustomerId for business partners. For assistance setting up a stored procedure, contact your VAR.

Note

The example stored procedure code in this topic is specific to one version of SAP Business One and database version. Square does not assist in deploying the stored procedure. Instead, your VAR should modify the example code to work in your environment with your version of SAP Business One and database before deploying it.

Link to section

Create a stored procedure

You need to know your SQL server and database names to complete this task.

  1. Start Microsoft SQL Server Management Studio.

  2. Sign in.

  3. In Object Explorer, choose [SQL Server Name], Databases, [Database Name], Programmability, and Stored Procedures.

    The Microsoft SQL Server management console, with the Stored Procedures folder selected.

    Did you know?

    You can press the F8 key or choose View and then Object Explorer in the menu bar if the Object Explorer window is not visible.

  4. Find the dbo.SBO_SP_TransactionNotification stored procedure.

    The SQL Server Management console, with the Stored Procedures folder and object selected.

  5. Right-click dbo.SBO_SP_TransactionNotification, and then choose Modify.

    The SQL Server Management console, with the Object selected and edit properties in the Stored Procedures folder.

  6. Copy all the SQL code in Square Bridge stored procedures.

  7. In Management Studio Console, paste the SQL code at the end of dbo.SBO_SP_TransactionNotification, just before the last four lines of the file (at -- ADD YOUR CODE HERE).

    The SQL Server Management console, with the Stored Procedures folder and the Object selected in the code editor.

  8. Run the updated stored procedure by choosing the Execute button (next to the red exclamation mark) on the Management Studio Console toolbar.

  9. If the stored procedures execute successfully, “Command(s) completed successfully” and “Query executed successfully” is displayed in the editor console.

    The SQL Server Management Console, with the Stored Procedures folder. The Object is selected in the execute procedure console.

    If the stored procedures did not execute successfully, be sure you have created the necessary user-defined fields. Execute the procedures again after creating the user-defined fields.

  10. Exit Microsoft SQL Server Management Studio. You do not need to save anything.

Link to section

Square Bridge stored procedures