Stored Procedures with FB_DBStoredProcedureRecordArray

With the help of the function block FB_DBStoredProceduresRecordArray parameters can be declared as INPUT, OUTPUT or INOUT and can be passed over to the stored procedures. So complex SQL-commands can be pre-programmed at the database server and only need to be triggered by the TwinCAT Database Server. The difference to the FB_DBStoredProceduresRecordReturn function block is, you get a number of records with only one function call.

Download "Example with stored procedures" sample9.zip

Stored Procedures with FB_DBStoredProcedureRecordArray 1:

Used database type

MS SQL (MS SQL Server 2008)

Compatible database types

MS SQL

Used function blocks

FB_DBStoredProceduresRecordArray

Integrated libraries

"TcDatabase.lib", "TcSystem.lib","TcBase.lib","TcStandard.lib"

Download data list

FB_DBStoredProceduresRecordArray_Sample.pro, CurrentConfigDataBase.xml, SQLQuery2.sql

The following example shows the call of a stored procedure with an input parameter and a return data set for a customer and production database sample. The procedure was generated at a Microsoft SQL Server 2008.

Code of the stored procedure SP_GetAddressByCustomerID

CREATE PROCEDURE [SP_GetAddressByCustomerID] 
    @Customer_ID bigint
AS
BEGIN
    SELECT tbl_Customer.ID, tbl_Customer.Name, tbl_Customer.Customer, tbl_Products.SerNum, tbl_Products.Product, tbl_Products.Info, tbl_Pos.Timestamp FROM 
        tbl_Pos JOIN tbl_Customer ON tbl_Pos.CustomerNum = tbl_Customer.ID 
        JOIN tbl_Products ON tbl_Pos.ProductNum = tbl_Products.SerNum
    WHERE
        tbl_Pos.CustomerNum = @Customer_ID;
END

Variable declaration in the PLC

PROGRAM MAIN
VAR
    R_TRIG1: R_TRIG;
    bREAD : BOOL := FALSE;

    nState: BYTE;

    arrParaList: ARRAY [0..0] OF ST_DBParameter;

    nCustomerID: DINT := 12345;

    FB_DBStoredProceduresRecordArray1: FB_DBStoredProceduresRecordArray;

    nCustomerID: DINT := 12345;
    nRecordStartIndex: UDINT;
    stRecordArr: ARRAY [1..25] OF ST_Record;
    nRecs: UDINT;

    bBusy: BOOL;
    bErr: BOOL;
    nErrid: UDINT;
    stSqlstate: ST_DBSQLError;
END_VAR

Data set structure in the PLC (ST_Record)

TYPE ST_Record :
STRUCT
    nID : T_ULARGE_INTEGER;
    sCustomer : STRING(50);
    sName : STRING(50);
    nProductNum : DINT;
    sProductName : STRING(50);
    sProductInfo : T_MaxString;
    tTimestamp : DT;
END_STRUCT
END_TYPE

PLC Program

R_TRIG1(CLK:=bREAD);
IF R_TRIG1.Q AND NOT bBusy THEN
    nState := 1;
END_IFCASE nState OF
    0:
        ;
    1:(*Init of the parameters*)
        arrParaList[0].sParameterName := '@Customer_ID';
        arrParaList[0].eParameterDataType := eDBColumn_Integer;
        arrParaList[0].eParameterType := eDBParameter_Input;
        arrParaList[0].cbParameterValue := SIZEOF(nCustomerID);
        arrParaList[0].pParameterValue := ADR(nCustomerID);

        nState := 2;
    2:(*Start the stored procedure "SP_GetCustomerPosition"*)
        FB_DBStoredProceduresRecordArray1(
            sNetID:= ,
            hDBID:= 1,
            sProcedureName:= 'SP_GetCustomerPositions',
            cbParameterList:= SIZEOF(arrParaList),
            pParameterList:= ADR(arrParaList),
            nStartIndex:= nRecordStartIndex,
            nRecordCount:= 25,
            cbRecordArraySize:= SIZEOF(stRecordArr),
            pDestAddr:= ADR(stRecordArr),
            bExecute:= TRUE,
            tTimeout:= T#15s,
            bBusy=> bBusy,
            bError=> bErr,
            nErrID=> nErrid,
            sSQLState=> stSqlstate,
            nRecords=> nRecs);
        
        IF NOT bBusy THEN
            FB_DBStoredProceduresRecordReturn1(bExecute:= FALSE);
            nState := 0;
        END_IFEND_CASE

Visualization

Stored Procedures with FB_DBStoredProcedureRecordArray 2: