Page History

Use stored procedures in dataset queries.

Reference → Modules → Datasets → UI → DBs → DatasetQuery

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

Creating a Stored Procedure

The procedure to create a Stored Procedure is summarized by the following steps:

1. Create a Database in the Database management system.
2. In this Database, Create Tables with primary keys.
3. Create a third Table containing these primary keys from the other Databases.
4. Execute the Procedure in the Database management system.
5. Save the result of the Query to a tag or .Net variable.
6. Execute the Stored Procedures.

The following sections address each of the above steps in detail.

Step 1: Create a Database

First, you need to create a Database using a management system. This example uses the SQL Server Management Studio (SSMS). Follow the steps below to create a database using SQL Server Management Studio:

In the New Query window, paste the code below.  It tells the SQL Server to switch to the master database and then create a new database called test.

USE master;
CREATE DATABASE test; GO

Step 2: Create Tables with primary keys

After creating the test Database, you need to create a Table with the primary keys (Spec Id and Char Id). Follow the steps below.

In the New Query window, paste the code below to create two tables, dbo.Characteristic and dbo.Specification , with the primary keys Char_Id  and Spec_ID , respectively. It also inserts sample data into both tables.

USE test GO
CREATE TABLE dbo.Characteristic (Char_Id int PRIMARY KEY NOT NULL,
Char_Desc varchar(25) NOT NULL, OptionsChar varchar(25))
GO
INSERT dbo.Characteristic (Char_Id, Char_Desc, OptionsChar) VALUES(10,’C010’,’Char1’),(15,’C015’,’Char2’),(19,’C019’,’Char3’),(14,’C014’,’Char4’),(18,’C018’,’Char5’),(17,’C017’,’Char6’),(16,’C016’,’Char7’)GO
CREATE TABLE dbo.Specification (Spec_ID int PRIMARY KEY NOT NULL,
Spec_Desc varchar(25) NOT NULL, OptionsSpec varchar(25))
GO
INSERT dbo.Specification (Spec_ID, Spec_Desc, OptionsSpec) VALUES
(30, ’Spec 30’, ’Spec1’),
(32, ’Spec 32’, ’Spec2’),
(33, ’Spec 33’, ’Spec3’),
(37, ’Spec 37’, ’Spec4’),
(34, ’Spec 34’, ’Spec5’),
(39, ’Spec 39’, ’Spec6’),
(35, ’Spec 35’, ’Spec7’) GO

Step 3: Create a Table containing the primary keys from the other Tables

The next step is to create a third table containing the primary keys from the previously created tables used to create the Store Procedure. Follow the next steps to create the new table:

In the New Query window, paste the code below. As a result, the system will create a new Table dbo.Active_Spec  containing the Primary Keys from the other two Tables dbo.Characteristic and dbo.Specification.It also inserts sample data into the dbo.Active_Spec table and creates a Stored Procedure named dbo.TestSP .

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

Step 4: Execute the Procedure

At this stage, you have created a Stored Procedure and can now execute it. To execute the Procedure recently created, follow the steps below:

In the New Query window, paste the code below, which will execute the TestSP stored procedure with the parameter value C019 .

EXEC TestSP ’C019’

;
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

Image Removed

Step 5: Save the result of a query

If your application has a Tag of the type Data Table named Test and a Dataset Query named Query1. You can populate the Test Tag using the following code:

@Tag.Test = @Dataset.Query.Query1.SelectComand()

Step 6: Executing Stored Procedures

Both Queries and Stored Procedures are defined in the Datasets → Queries table. To execute a Stored Procedure, use the ExecuteCommand() method. For example,  Dataset.Queries.Query1.ExecuteCommand().


When passing parameters, you can use the @null@ syntax to pass a null value as a parameter, as in the below example:

Exec TestOutput @return_Value = {Tag.ReturnValue} RETURN_VALUE, @vcrPrefix = @null@, @intNextNumber = {Tag.NextNumber} OUTPUT, @vcrFullLicense = {Tag.NextLicense} OUTPUT 

Configuring the Solution

To configure and use Stored Procedures in the solution, follow the steps below:

A button is configured to run a Script using the code presented below:

public void RunSP_Btn(object sender, System.Windows.Input.InputEventArgs e)
{
string sqlStatement = string.Format("exec TestSP ’{0}’", "C019"); @Dataset.Query.Query_SqlServer.SqlStatement = sqlStatement;

string sts = ""; @Dataset.Query.Query_SqlServer.ExecuteCommandWithStatus(out sts);
}

Image Removed

In this section...