Procedures

With Microsoft SQL Server, you can configure so-called stored procedures.

These procedures can have input parameters, which can also be defined as OUT parameters. The OPC Router supports both, so you can both transfer values and receive them back.

Complex logic can be mapped in the procedure using the T-SQL programming language.

In addition, the OPC Router allows you to query so-called result sets. This provides the last table select executed in the procedure as a result.

warning

Please note that the OPC Router queries the result set once by calling the procedure. This is done by the user confirming the "Get Result Set" button in the OPC Router.

The procedure is executed within a transaction that is subsequently reversed. To determine the structure of the result set, the procedure is called in a transaction in which all parameters are set to NULL. The transaction is then canceled.

If the parameters have the "Allow Null" option, the procedure is called only with NULL values. Otherwise, the procedure is called with the default data types, for example, "0" for numeric parameters or an empty string for character strings.

Creating a stored procedure

CREATE PROCEDURE sp_myProcedure
    @nMyValue INT,
    @strText NVARCHAR(50) OUT
AS
BEGIN
    SET @strText = 'I received ' + CAST(@nMyValue as nvarchar)  + ' as value...';
END
GO