Stored Procedures with MS SQL

No "stored procedures" can be generated or configurated with the Database Server.

Stored procedures can be carried out with the function blocks FB_DBStoredProcedures and FB_DBStoredProceduresRecordReturn from the version 1.0.13 on.

With the help of these function blocks 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.

Download "Example with stored procedures" sample4.zip

Stored Procedures with MS SQL 1:

Used database type

MS SQL (MS SQL Server 2008)

Compatible database types

MS SQL

Used function blocks

FB_DBStoredProceduresRecordReturn

Integrated libraries

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

Download data list

FB_DBStoredProcedures_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;
    nRecordIndex: UDINT;
    stRecord: ST_Record;
    nRecs: UDINT;

    FB_DBStoredProceduresRecordReturn1: FB_DBStoredProceduresRecordReturn;

    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;
    sName : STRING;
    nProductNum : DINT;
    sProductName : STRING;
    sProductInfo : STRING;
    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_DBStoredProceduresRecordReturn1(
            sNetID:= ,
            hDBID:= 1,
            sProcedureName:= 'SP_GetCustomerPositions',
            cbParameterList:= SIZEOF(arrParaList),
            pParameterList:= ADR(arrParaList),
            nRecordIndex:= nRecordIndex,
            cbRecordSize:= SIZEOF(stRecord),
            pRecordAddr:= ADR(stRecord),
            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 MS SQL 2: