Overview

This example demonstrates how to use the Redshift Execute Snap to invoke stored procedures with parameters. The pipeline shows both creating a stored procedure and calling it with input and output parameters.

Prerequisites

• Amazon Redshift cluster with appropriate access
• Valid Redshift account configured in SnapLogic
• Permissions to create and execute stored procedures
• Understanding of Redshift stored procedure syntax

Pipeline Overview

The pipeline consists of two stages:

1. Create a stored procedure using the Redshift Execute Snap
2. Call the stored procedure and retrieve the output values

Creating a Stored Procedure

Step 1: Define the Stored Procedure

Create a stored procedure named sp_inout_proc with three parameters: a, b, and c.

The stored procedure performs calculations:

CREATE OR REPLACE PROCEDURE sp_inout_proc (INOUT a int, b int, INOUT c int)
AS \$\$
BEGIN
  a := b * a;
  c := b * c;
END;
\$\$

Execution Output:

On validation, the stored procedure is created and the Snap outputs the execution status:

Calling the Stored Procedure

Step 2: Configure the Mapper Snap

Use a Mapper Snap to provide input parameters for the stored procedure call.

Step 3: Execute the Stored Procedure

Configure another Redshift Execute Snap to call the stored procedure with the three parameter values:

Output with Results:

After execution, the Snap retrieves the values from the procedure and displays the output:

Parameter Types

Redshift stored procedures support three parameter types:

• IN: Input-only parameters passed to the procedure
• OUT: Output-only parameters returned by the procedure
• INOUT: Parameters that serve as both input and output

In this example, parameters a and c are INOUT parameters, while b is an IN parameter.

Use Cases

This pattern is useful for:

• Executing complex business logic stored in the database
• Performing calculations that require multiple steps
• Encapsulating frequently used operations in reusable procedures
• Implementing data validation and transformation logic
• Maintaining data integrity through stored procedure constraints

Best Practices

• Escape the $ character in procedure definitions using \$
• Use INOUT parameters when you need both input and output values
• Test stored procedures independently before integrating into pipelines
• Handle exceptions within stored procedures for better error management
• Document parameter types and expected return values
• Use descriptive procedure names that indicate their purpose