Skip to main content

Stored Procedure

The Stored Procedure activity executes a SQL Server stored procedure from your workflow.

Purpose​

Use the Stored Procedure activity to:

  • Execute custom data transformations
  • Perform complex calculations
  • Implement business logic in SQL
  • Call existing stored procedures
  • Return values to the workflow

Configuration​

Stored Procedure Selection​

  • Select Procedure: Choose from stored procedures in the AppDB schema
  • Only procedures in AppDB are available for workflow execution

Parameters​

Input Parameters​

  • Add Parameters: Define parameters the stored procedure expects
  • Parameter Name: Must match the stored procedure parameter name
  • Parameter Value: Can be:
    • Static values
    • Workflow parameters: {ParameterName}
    • Calculated values

Output Parameters​

  • Return Values: Stored procedures can return values
  • Context Key: Specify workflow variable to store the returned value

Timeout Settings​

Default Timeout​

  • 30 seconds: Standard timeout for stored procedure execution
  • Execution is forcibly stopped if timeout is exceeded

Increase Timeout​

  • Option Available: Can extend timeout for longer-running procedures
  • Recommended Maximum: 2 minutes
  • Warning: Procedures taking more than 2 minutes may indicate performance issues

Stored Procedure Requirements​

Location​

All stored procedures must be in the AppDB schema:

CREATE PROCEDURE AppDB.MyStoredProcedure
    @ParameterName DataType
AS
BEGIN
    -- Procedure logic
END

Parameter Matching​

  • Workflow parameters must match stored procedure parameter names exactly
  • Parameter data types should be compatible

Return Values​

To return a value to the workflow:

CREATE PROCEDURE AppDB.GetNextClientID
    @CurrentClientID VARCHAR(50) OUTPUT
AS
BEGIN
    SELECT @CurrentClientID = MIN(ClientID)
    FROM Clients
    WHERE ClientID > @CurrentClientID
    
    IF @CurrentClientID IS NULL
        SET @CurrentClientID = '###END###' -- Sentinel value
END

Example Configurations​

Simple Execution (No Parameters)​

Stored Procedure: AppDB.RefreshMaterializedView
Parameters: [none]
Increase Timeout: [unchecked]

With Input Parameter​

Stored Procedure: AppDB.WeeklyAggregation
Parameters:
  └─ Name: WeeklyAggregation
     Value: {Year}
Increase Timeout: [unchecked]

With Output Parameter​

Stored Procedure: AppDB.GetNextClientID
Parameters:
  └─ Name: CurrentClientID
     Value: {ClientID}
     Output Context Key: ClientID
Increase Timeout: [unchecked]

Long-Running Procedure​

Stored Procedure: AppDB.ComplexCalculation
Parameters:
  └─ Name: CalculationType
     Value: Full
Increase Timeout: [checked]
Extended Timeout: 120 seconds

Common Use Cases​

Data Transformation​

Stored Procedure: AppDB.TransformStagingData
Purpose: Transform data from staging format to model format

Calculations​

Stored Procedure: AppDB.CalculateMonthlyTotals
Parameters: Month, Year
Purpose: Calculate and store monthly aggregated values

Iteration Logic​

Stored Procedure: AppDB.GetNextClientID
Parameters: CurrentClientID (input/output)
Purpose: Return next client in sequence for processing loop

Data Validation​

Stored Procedure: AppDB.ValidateImportedData
Parameters: StagingTableID
Purpose: Validate data quality before processing
Returns: ValidationResult (Pass/Fail)

Detailed Example from Transcript​

Client Processing Loop​

Stored Procedures Involved​

1. Weekly Aggregation

Stored Procedure: AppDB.WeeklyAggregation
Parameter: WeeklyAggregation = {Year}
Purpose: Aggregate daily data to weekly buckets
Note: Required because data had multiple records per client per day

2. Get Next Client ID

Stored Procedure: AppDB.GetNextClientID
Parameter: ClientID (input/output)
  └─ First call: Input = null → Returns first client
  └─ Next calls: Input = previous client → Returns next client
  └─ Last call: No more clients → Returns "###END###"

Logic: 
  SELECT next ClientID WHERE ClientID > @CurrentClientID
  ORDER BY ClientID
  Return first result (alphabetically next)

Workflow Pattern​

1. Script: Initialize
   └─ {ClientID} = null

2. [Loop Start] Stored Procedure: GetNextClientID
   └─ Input: {ClientID}
   └─ Output: {ClientID} (updated with next client)

3. Log Message
   └─ "Processing client: {ClientID}"

4. If: Check for end
   └─ Condition: {ClientID} == "###END###"
   └─ [No] Continue processing
   └─ [Yes] End (no arrow, workflow stops)

5. Stored Procedure: ProcessForecastForClient
   └─ Parameter: ClientID = {ClientID}

6. Loop back to step 2

Parameter Handling​

Static Values​

Parameters:
  └─ Status = "Active"
  └─ RecordType = 1

Workflow Parameters​

Parameters:
  └─ ClientName = {ClientName}
  └─ ProcessDate = {Date}
  └─ UserID = {CurrentUser}

Calculated Values​

Parameters:
  └─ Year = DateTime.Now.Year
  └─ FullPath = {BasePath} + "\\" + {FileName}

Optional Parameters​

If workflow parameter is empty:

  • Null or Empty String is passed to stored procedure
  • Stored procedure should handle null values
  • Can provide defaults in stored procedure definition
CREATE PROCEDURE AppDB.MyProcedure
    @OptionalParam VARCHAR(50) = 'DefaultValue'
AS
BEGIN
    -- Use COALESCE or ISNULL for runtime null handling
    SET @OptionalParam = COALESCE(@OptionalParam, 'FallbackValue')
END

Return Value Handling​

Single Return Value​

Context Key: Result

Stored Procedure returns a single value:
  └─ Stored in {Result}
  └─ Available to subsequent activities

Multiple Return Values​

For multiple values:

  1. Option 1: Use multiple OUTPUT parameters
  2. Option 2: Return result set and process with another activity
  3. Option 3: Write to temporary table and read separately

Sentinel Values​

Use special values to indicate conditions:

"###END###" = No more records
"###ERROR###" = Error occurred
"" (empty) = Not found
-1 = Invalid operation

Timeout Considerations​

Why Timeouts Matter​

From Transcript:

"A stored procedure by default has 30 seconds to finish, otherwise it's going to be forcibly stopped."

"Once we get to things that take two minutes, you should really question whether it's doing things in the right way, if there's something really wrong."

When to Increase Timeout​

Good Reasons:

  • Large batch processing
  • Complex aggregations over big datasets
  • Data migrations
  • Year-end calculations

Warning Signs:

  • Regularly hitting 2-minute timeout
  • Procedures getting slower over time
  • May indicate:
    • Missing indexes
    • Inefficient queries
    • Growing data not handled properly
    • Need for optimization

Best Practices​

  • Start with default 30-second timeout
  • Increase only if necessary
  • If approaching 2 minutes, investigate optimization
  • Consider breaking into smaller steps
  • Use proper indexing
  • Consider batch processing patterns

Performance Optimization​

Index Usage​

-- Ensure proper indexes exist
CREATE NONCLUSTERED INDEX IX_Clients_ClientID 
ON Clients(ClientID)

Batch Processing​

Instead of one long procedure:

1. Get batch of IDs to process
2. Process batch in stored procedure (< 30 seconds)
3. Loop back until complete

Temporary Tables​

Use carefully:

  • Can improve performance for complex queries
  • May cause issues if multiple instances run in parallel
  • Consider Lock Subprocess for procedures using temp tables

Parallel Execution Concerns​

Problem: Temp Tables and Parallel Execution​

From Transcript (Historical Example):

"At Fluence we had... the whole engine was calculating the end results, and unfortunately, if you started two of them, it created lots and lots of problems because they used temporary tables that had data, and then one stored procedure, one instance was writing something in that temporary table, the other one was taking stuff from there and deleting."

Solution: Lock Subprocess​

For stored procedures using temp tables or shared state:

Lock Subprocess: "ModelCalculationLock"
  └─ Stored Procedure: AppDB.CalculateModel

This ensures only one instance runs at a time, even across different workflows.

Error Handling​

Stored Procedure Errors​

  • Execution failures are logged in workflow execution log
  • Workflow will stop at the failed stored procedure activity
  • Error messages from SQL Server are captured

Handling Expected Errors​

1. Stored Procedure execution

2. If (execution succeeds)
   └─ Continue processing

3. If (execution fails)
   └─ Log error
   └─ Send notification
   └─ Stop or retry

Return Value Validation​

1. Stored Procedure returns status

2. If ({Status} == "Success")
   └─ Continue

3. If ({Status} == "Error")
   └─ Handle error case

Best Practices​

  • Keep Procedures Short: Target < 30 seconds
  • Use Parameters: Don't hardcode values
  • Return Values: Use for status and key information
  • Error Handling: Include error handling in stored procedures
  • Documentation: Comment stored procedure logic
  • Logging: Log key operations within procedures
  • Transactions: Use transactions for data consistency
  • Indexing: Ensure proper indexes exist
  • Testing: Test with production-scale data

Comparison to ETL Activity​

FeatureStored ProcedureETL
FlexibilityUnlimited custom logicStructured transformations
ConfigurationSQL codeVisual configuration
Error HandlingManualAutomatic
LoggingManualAutomatic
ReusabilityCode reuseDefinition reuse
Learning CurveSQL knowledge requiredEasier for non-developers

Use Stored Procedure when:

  • Logic is too complex for ETL
  • Need precise control over operations
  • Implementing custom algorithms
  • Working with existing stored procedures

Use ETL when:

  • Standard data loading patterns
  • Want automatic validation and logging
  • Prefer visual configuration
  • Need reusable import definitions

Debugging Tips​

Log Messages​

Add Log Message activities around stored procedure calls:

1. Log: "Starting procedure {ProcedureName} with param: {ParamValue}"
2. Stored Procedure execution
3. Log: "Procedure completed, result: {ResultValue}"

Test Stored Procedures Independently​

Before using in workflows:

-- Test in SQL Server Management Studio
DECLARE @Result VARCHAR(50)
EXEC AppDB.MyProcedure @Parameter = 'TestValue', @Result = @Result OUTPUT
SELECT @Result

Check Execution Plans​

For performance issues:

  • Review actual execution plans
  • Look for table scans, missing indexes
  • Check statistics are up to date

Security Notes​

  • Only AppDB schema procedures are accessible
  • Workflow service account executes procedures
  • Ensure proper permissions are granted
  • Avoid dynamic SQL with user input
  • Validate parameters within procedures
  • Log sensitive operations for audit
  • ETL: Alternative for standard data loading
  • Script: For C# logic instead of SQL
  • Lock Subprocess: For serializing access to shared resources
  • If: For conditional execution based on stored procedure results
  • Log Message: For debugging stored procedure calls

Summary​

The Stored Procedure activity provides unlimited flexibility for implementing custom logic in workflows:

Strengths:

  • Full SQL Server capabilities
  • Custom business logic
  • Integration with existing procedures
  • Return values to workflow
  • Complex data operations

Considerations:

  • Requires SQL knowledge
  • Performance monitoring needed
  • Timeout management
  • Parallel execution concerns
  • Manual error handling

Best suited for custom logic that can't be achieved with standard workflow activities, or when leveraging existing stored procedure investments.