Result set batch filter - MSSQL

A result set batch filter is invoked (potentially) when a result set is received on a connection with the batch parameters set. The batch contains one or more rows, up to the maximum specified in the connection parameters.

The filter can modify the rows, add more rows, remove rows, or return an error. This filter works for all result sets, whether they result from SQL statements or from the execution of a stored procedure.

Parameters

All parameters are optional. If no parameter is specified, then the filter will be invoked for every row of every result set.

Query pattern

Optional. A string or regular expression. If the current SQL does not match this, the filter will not be invoked. The current SQL is the last SQL command executed, either directly or via the sp_executesql, sp_prepexec or sp_prepexecrpc stored procedures.

Client IPs

If specified, a comma-separated (or line break separated) list of IP4 or IP6 addresses or regular expressions for IP addresses.

Users

If specified, a comma-separated (or line break separated) list of user names or regular expressions for user names.

Columns

If specified, a comma-separated list of strings or regular expressions. The filter will be invoked only for result sets that contain at least one column whose name matches one of the specified strings or regular expressions. Note that the name of the table or schema is not available, only the name of the column, which will be the alias if the column is aliased in the query.

Examples

Hide a row

for (const row of context.packet) {

if (row.customer_country === "FR" || row.customer_country === "DE") {

row.remove();

}

}

Clone and insert a row

for (const row of context.packet) {

if (row.customer_name === "Jones") {

const clone = row.clone();

clone.customer_name += " (copy)";

context.packet.insertRowAfter(clone, row);

}

}

Example with data classification

Assuming that the database contains some data classification:

if (context.packet[0].columnHasSensitivity("first_name", "Confidential")) {
for (const row of context.packet) {

row.first_name = "<?>";
}

}

This will replace the contents of the first_name column with <?> if that column has a data classification sensitivity of Confidential.