MsSQL Stored Procedure Read Documentation

Version: 4.0.0 

Retrieved: 2025-10-09 15:16:06

Microsoft SQL Server Stored Procedure Read

This module executes a stored procedure on a Microsoft SQL Server database with configurable input and output parameter mapping. It supports both result sets and output parameters, storing them in separate properties to avoid conflicts.

Settings

Credential

This module needs a credential of type 'Connection string' to connect to the database server. A minimal connection string is shown below. Depending on the setup of your database server additional parameters may be needed. Please consult the documentation for your server.

Server=tcp:192.168.0.5;Database=crosser;User ID=demo;Password=crosser123;

NOTE: From version 2.0.0 the default off the Encrypt parameter has changed from false to true, if you rely on it being set to false then you need to specify it

Server=tcp:192.168.0.5;Database=crosser;User ID=demo;Password=crosser123;Encrypt=false;

To set a custom command timeout (in seconds) for SQL operations:

Server=tcp:192.168.0.5;Database=crosser;User ID=demo;Password=crosser123;Encrypt=false;Command Timeout=60

Input

The stored procedure is executed each time a message is received. Input parameters are mapped from the incoming message using the configured Input Parameters mappings. Each input parameter can specify a property path to extract values from the message (e.g., data.userId, header.timestamp).

Output

An output message is sent each time the module processes a message. The module handles two types of outputs separately:

1. Result Set: If the stored procedure returns rows (SELECT statements), they are stored in the configured Target Property as an array of objects
2. Output Parameters: Each output parameter is stored using its own configured target property path, completely separate from the result set

Examples

Example 1: Stored Procedure with Result Set

Executes a procedure that returns sensor data.

Stored Procedure:

CREATE PROCEDURE GetSensorData
    @SensorType NVARCHAR(50),
    @MinReading INT
AS
BEGIN
    SELECT name, reading FROM sensors 
    WHERE type = @SensorType AND reading >= @MinReading
END

Module Configuration:

Procedure Name: GetSensorData
Target Property: sensors

Input Parameters:
- Name: SensorType, Source Property: request.sensorType
- Name: MinReading, Source Property: filters.minValue

Input Message:

{
    "request": {
        "sensorType": "temperature"
    },
    "filters": {
        "minValue": 100
    }
}

Output Message:

{
    "request": {
        "sensorType": "temperature"
    },
    "filters": {
        "minValue": 100
    },
    "sensors": [
        {
            "name": "sensor3",
            "reading": 129
        },
        {
            "name": "sensor4",
            "reading": 489
        }
    ]
}

Example 2: Stored Procedure with Output Parameters Only

Executes a mathematical operation and returns the result via output parameter.

Stored Procedure:

CREATE PROCEDURE AddNumbers
    @Num1 INT,
    @Num2 INT,
    @Result INT OUTPUT
AS
BEGIN
    SET @Result = @Num1 + @Num2
END

Module Configuration:

Procedure Name: AddNumbers
Target Property: out

Input Parameters:
- Name: Num1, Source Property: data.firstNumber
- Name: Num2, Source Property: data.secondNumber

Output Parameters:
- Name: Result, Target Property: result

Input Message:

{
    "data": {
        "firstNumber": 15,
        "secondNumber": 25
    }
}

Output Message:

{
    "data": {
        "firstNumber": 15,
        "secondNumber": 25
    },
    "result": 40
}

Example 3: Stored Procedure with Both Result Set and Output Parameters

Executes a procedure that returns both data rows and status information.

Stored Procedure:

CREATE PROCEDURE ProcessOrders
    @CustomerID INT,
    @ProcessedCount INT OUTPUT,
    @Status NVARCHAR(50) OUTPUT
AS
BEGIN
    SELECT OrderID, OrderDate, Total 
    FROM Orders 
    WHERE CustomerID = @CustomerID AND Status = 'Pending'
    
    SET @ProcessedCount = @@ROWCOUNT
    SET @Status = 'Completed'
END

Module Configuration:

Procedure Name: ProcessOrders
Target Property: orders

Input Parameters:
- Name: CustomerID, Source Property: customer.id

Output Parameters:
- Name: ProcessedCount, Target Property: (empty - uses default)
- Name: Status, Target Property: (empty - uses default)

Input Message:

{
    "customer": {
        "id": 12345
    }
}

Output Message:

{
    "customer": {
        "id": 12345
    },
    "orders": [
        {
            "OrderID": 1001,
            "OrderDate": "2023-12-01",
            "Total": 150.00
        },
        {
            "OrderID": 1002,
            "OrderDate": "2023-12-02",
            "Total": 275.50
        }
    ],
    "out": {
        "ProcessedCount": 2,
        "Status": "Completed"
    }
}

Migration from Legacy Parameter Property

If you're upgrading from the legacy Parameter Property approach, follow these steps:

1. Remove the old Parameter Property setting
2. Configure Input Parameters individually, mapping each to a specific property path
3. Configure Output Parameters if your stored procedure has any, specifying target properties
4. Update your flow logic to read output parameters from their new target properties instead of the main target property

New Hierarchical Naming Convention

Output parameters now use hierarchical naming by default:

• Old naming: out_Result, out_Status (underscore separator)
• New naming: out.Result, out.Status (dot notation, consistent with other modules)

This creates a cleaner structure where all output parameters are grouped under the target property as nested objects.

This new approach provides much better control and prevents conflicts between result sets and output parameters.