DatasetQuery Stored Procedures (Reference)

Datasets → Tutorial | Concept | How-to Guide | Reference

Configuration in FrameworX

Query Setup

1. Navigate to Datasets → Queries
2. Create new query
3. Set SqlStatement to procedure call:

sql

EXEC ProcedureName @Param1 = {{Tag.Value1}}, @Param2 = {{Tag.Value2}}

Execution Syntax

Basic Execution

sql

-- Simple procedure
EXEC GetProductionData

-- With parameters
EXEC GetProductionByDate @Date = '{{Tag.QueryDate}}'

-- Multiple parameters
EXEC UpdateBatch 
  @BatchID = {{Tag.Batch_ID}},
  @Status = {{Tag.Production_Status}},
  @Operator = '{{Tag.Operator_Name}}'

Output Parameters

sql

EXEC CalculateTotals
  @StartDate = '{{Tag.StartDate}}',
  @EndDate = '{{Tag.EndDate}}',
  @Total = {{Tag.TotalProduction}} OUTPUT,
  @Average = {{Tag.AverageRate}} OUTPUT

Return Values

sql

EXEC ValidateData
  @BatchID = {{Tag.Batch_ID}},
  @ReturnValue = {{Tag.ValidationResult}} RETURN_VALUE

Null Parameters

Use @null@ for null values:

sql

EXEC UpdateRecord 
  @ID = {{Tag.RecordID}},
  @Notes = @null@,
  @ModifiedBy = '{{Tag.Username}}'

Execution Methods

Using ExecuteCommand

csharp

// Execute stored procedure
@Dataset.Query.MyProcedure.ExecuteCommand();

// With status check
string status;
@Dataset.Query.MyProcedure.ExecuteCommandWithStatus(out status);

if (status == "OK")
{
    // Process success
}

Dynamic SQL Building

csharp

public void RunStoredProcedure()
{
    // Build procedure call
    string sql = string.Format(
        "EXEC GetDataByLine @Line = '{0}', @Date = '{1}'",
        @Tag.ProductionLine,
        DateTime.Now.ToString("yyyy-MM-dd")
    );
    
    @Dataset.Query.ProcedureQuery.SqlStatement = sql;
    
    // Execute
    string status;
    @Dataset.Query.ProcedureQuery.ExecuteCommandWithStatus(out status);
    
    if (status == "OK")
    {
        @Tag.ExecutionResult = "Success";
    }
}

Creating Stored Procedures

SQL Server Example

sql

CREATE PROCEDURE [dbo].[GetProductionSummary]
    @StartDate datetime,
    @EndDate datetime,
    @LineID int = NULL,
    @TotalCount int OUTPUT,
    @AvgRate float OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Calculate totals
    SELECT @TotalCount = COUNT(*),
           @AvgRate = AVG(ProductionRate)
    FROM Production
    WHERE Timestamp BETWEEN @StartDate AND @EndDate
      AND (@LineID IS NULL OR LineID = @LineID);
    
    -- Return detailed data
    SELECT LineID, 
           SUM(Count) as Total,
           AVG(Rate) as AvgRate
    FROM Production
    WHERE Timestamp BETWEEN @StartDate AND @EndDate
      AND (@LineID IS NULL OR LineID = @LineID)
    GROUP BY LineID;
    
    RETURN 0; -- Success
END

PostgreSQL Example

sql

CREATE OR REPLACE FUNCTION get_production_summary(
    start_date timestamp,
    end_date timestamp,
    line_id integer DEFAULT NULL
)
RETURNS TABLE(
    line_id integer,
    total_count bigint,
    avg_rate numeric
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT p.line_id,
           COUNT(*) as total_count,
           AVG(p.rate) as avg_rate
    FROM production p
    WHERE p.timestamp BETWEEN start_date AND end_date
      AND (line_id IS NULL OR p.line_id = line_id)
    GROUP BY p.line_id;
END;
$$;

Parameter Types

Complete Example

1. Database Setup

sql

-- Create tables
CREATE TABLE Characteristic (
    Char_Id int PRIMARY KEY,
    Char_Desc varchar(25),
    OptionsChar varchar(25)
);

CREATE TABLE Specification (
    Spec_ID int PRIMARY KEY,
    Spec_Desc varchar(25),
    OptionsSpec varchar(25)
);

CREATE TABLE Active_Spec (
    AS_ID int PRIMARY KEY,
    Char_ID int,
    Spec_ID int,
    OptionsAS varchar(25)
);

2. Create Procedure

sql

CREATE PROCEDURE [dbo].[TestSP]
    @Char_Desc varchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @Char_Id int;
    
    SELECT @Char_Id = Char_Id 
    FROM Characteristic 
    WHERE Char_Desc = @Char_Desc;
    
    SELECT c.Char_Desc, 
           s.Spec_Id, 
           s.Spec_Desc,
           c.OptionsChar,
           s.OptionsSpec,
           asp.OptionsAS
    FROM Specification s
    JOIN Active_Spec asp ON asp.Spec_Id = s.Spec_Id
    JOIN Characteristic c ON c.Char_Id = asp.Char_Id
    WHERE c.Char_Id = @Char_Id;
END

3. Configure in FrameworX

sql

-- Query configuration
EXEC TestSP @Char_Desc = '{{Tag.CharacteristicDesc}}'

4. Execute from Script

csharp

public void ExecuteProcedure()
{
    @Tag.CharacteristicDesc = "C019";
    
    string status;
    @Dataset.Query.TestProcedure.ExecuteCommandWithStatus(out status);
    
    if (status == "OK")
    {
        // Results available in mapped tags
        DataTable results = @Tag.ProcedureResults;
    }
}

Best Practices Checklist 

• Use parameters - Prevent SQL injection
• Handle errors - Check return values
• Validate inputs - Before execution
• Use transactions - For multi-step operations
• Document procedures - Clear parameter descriptions
• Test thoroughly - All parameter combinations
• Monitor performance - Execution plans

Troubleshooting

Procedure not found:

• Verify procedure exists
• Check schema/owner
• Confirm permissions

Parameter errors:

• Match parameter names exactly
• Check data types
• Verify nullable parameters

Output not returned:

• Confirm OUTPUT keyword
• Check tag mapping
• Verify execution method

Performance issues:

• Review execution plan
• Add indexes
• Optimize procedure logic

In this section...