How to execute dynamic SQL using a packaged query for SQL Server datasource?

This article will explain how to execute dynamic SQL using a packaged query for SQL Server datasource.

Here are few things to note about 'Packaged Queries'

1. It is a procedure that may bypass the TDV optimizer and execute data-source-specific SQL directly in the physical data source.
2. The query is defined in the native syntax and language of the data source where it will execute. The query is sent as a “package” to the physical data source for execution.
3. It can contain one, or a sequence of, statements that the target data source would understand (including DDL, analytical functions, unsupported functions, etc.)
4. It can only return data from the last statement, which should be a query with output fields matching a return cursor defined for this packaged query
5. TDV will only substitute parameters with input values Add WHERE clause if the single select box is checked.

TDV SQL procedure supported data types are:

•  Binary: BINARY, BLOB, VARBINARY
•  Decimal: DECIMAL, DOUBLE, FLOAT, NUMERIC
•  Integer: BIGINT, BIT, INTEGER, SMALLINT, TINYINT
•  String: CHAR, CLOB, LONGVARCHAR, VARCHAR
•  Time: DATE, TIME, TIMESTAMP
•  Complex: CURSOR, XML

If you want to pass multiple values to the IN clause then TDV does not have a direct way. This code can be used to execute any dynamic SQL.

Step 1: Create TDV procedure to create SQL command:
PROCEDURE AcceptMultipleValues(OUT result CURSOR(CustomerContactPhone VARCHAR(30)))
    --(IN multipleValues VECTOR(VARCHAR), OUT result CURSOR(CustomerContactPhone VARCHAR(30)))
    BEGIN
        DECLARE sqlCommand VARCHAR(1000);
        set sqlCommand = 'select * from item';
        call print(sqlCommand);
        call /shared/test/"multiple values"/executeDynamicQuery(result, sqlCommand);
    END

Step 2: Create a Packaged query and define one input parameter to accept the SQL command and output parameter to return cursor. 

In package query, we are using 'EXEC sp_executesql', used to executing Dynamic SQL Using sp_executesql on the SQL Server database.

Packaged query code:

<version 2>
EXEC sp_executesql N'{0}'