These features are supported for the following project types:

• Snowflake

• Azure Synapse

• SQL Server

Overview

User-Defined Functions and Stored-Procedures are ways to augment the capabilities of simple SQL. These tools allow users to perform procedural logic, error handling, and dynamic SQL generation, among other capabilities not possible with standard SQL.

Although similar in usage and DDL syntax, Procedures and Functions differ in several important ways:

• Procedures are called independently, while Functions are called within a SELECT context.

• Procedures can access and manipulate the database while Functions can not.

• Values returned from a Procedure are optional and can not be used in SQL. The opposite is true for Functions.

Please refer to the documentation for a full reference on Procedures and Functions:

• Snowflake documentation.

• SQL Server and Synapse documentation.

Support in SqlDBM

Procedures and Functions are SQL scripts, not relational objects like tables and views. As such, they can not be displayed directly on a diagram. However, users can still maintain them as part of their Project.

SqlDBM supports:

• Reverse Engineering

• Creating

• Editing

• Forward Engineering

• Adding to a diagram

• Connecting to objects through virtual relationships

• All extended functionality that applies to the above (e.g., compare revisions, alter scripts, etc.)

Creating Stored-Procedures and User-Defined Functions

Stored-Procedures and User-Defined Functions can be created and edited from the Database Explorer screen in SqlDBM. To do so, follow the steps below:

1. Click on the “Database Explorer” menu icon in the left menu bar

2. Select “Functions” or "Procedures" from the list of objects

3. Create (a) or Edit (b) the object.

   1. Click “Create New” to create a new Function or Procedure from a template.

   2. Click “Edit” to edit an existing Function or Procedure.

4. Enter the properties and details of the script in the on-screen editor.

5. When finished, click the blue "Analyze all Functions/Procedures" button to parse and validate the script.

Reviewing Function and Procedure Properties

Users can view the properties associated with Stored-Procedures and User-Defined Functions by selecting them from the Database Explorer screen in SqlDBM. The properties themselves are not editable. They are informed by parsing the script in question using the Analyze button.

Properties include:

• Schema

• Name

• Parameter list

Adding Functions and Procedures to a Diagram

Add Functions and Procedures to a diagram just like you would with a table.

From the "Diagram Explorer" screen, open the Functions or Procedures dropdowns to display available objects, or search for them by name.

Click on "Bring to Diagram" or use the icon shortcut to add the object to the selected diagram.

Creating relationships

Functions and Procedures can be connected/related to other objects on a diagram using virtual relationships.

To do this, select a virtual relationship type from the top menu of the diagram, then click and drag from a function or procedure to the target object.

Virtual connections can be made between any objects on the diagram (views, tables, functions, or procedures).

Naming and Overloading Functions and Procedures

Function and Procedure scripts can be "overloaded" by considering the parameter list as part of the object name. This allows users to "overload" the script by creating multiple versions which handle different parameter types differently.

The following naming is allowed and will be considered as two unique objects within a project and in the database:

• Create Procedure new_script (n float)...

• Create Procedure new_script (n integer)...

The following naming is not allowed and will result in an error:

• Create Procedure new_script (n float)...

• Create Procedure new_script (n float)...