TRANSFORM_SP_EXECUTE_SQL_STRING_UDF(STRING, STRING, ARRAY, ARRAY)

Definition

This user-defined function (UDF) emulates the behavior of embedded parameters (Data Binding) in the SP_EXECUTESQL system procedure by directly replacing their values in the SQL string.

Additionally, it removes the OUTPUT parameters from the string as this is done outside the EXECUTE IMMEDIATE to which the SP_EXECUTESQL will be transformed.

For more information, check the SP_EXECUTESQL translation specification.

TRANSFORM_SP_EXECUTE_SQL_STRING_UDF(
    _SQL_STRING STRING, 
    _PARAMS_DEFINITION STRING,
    _PARAMS_NAMES ARRAY,
    _PARAMS_VALUES ARRAY
)

Parameters

_SQL_STRING STRING

The string to be transformed.

_PARAMS_DEFINITION STRING

The original parameters definition checks the order in which parameter values must be assigned.

_PARAMS_NAMES ARRAY

The array of parameter names to replace the values in the SQL string.

_PARAMS_VALUES ARRAY

The array of the parameter values to be replaced in the SQL string.

Returns

Returns a STRING with the embedded parameters values replaced.

Usage example

Input:

SELECT TRANSFORM_SP_EXECUTE_SQL_STRING_UDF(
    'SELECT * FROM PERSONS WHERE NAME LIKE (@NAME) AND ID < @id AND AGE < @age;', '@age INT, @id INT, @name VARCHAR(25)',
    ARRAY_CONSTRUCT('', '', ''),
    ARRAY_CONSTRUCT(30, 100, 'John Smith'));

Output:

SELECT * FROM PERSONS WHERE NAME LIKE ('John Smith') AND ID < 100 AND AGE < 30;