mssql_param
Purpose
mssql_param extracts one value that a recorded SQL Server RPC binds to its statement: a parameter of an sp_executesql, sp_prepexec or sp_execute, or of a stored procedure called by name. Pair it with var_store to reuse the value, for example in a mocked result, or with a transform that rewrites it.
It applies only to requests, and only to RPCs. SQL batches carry their values as literals in the T-SQL; reach those with sql_literal. To make a mock match on bound values, use sql_key_params.
Usage
"type": "mssql_param",
"config": {
"index": "1"
}
- index - the position of the bound value, counting from 1. The statement and parameter declaration an
sp_executesqlorsp_prepexecsends first, and the handle ansp_executesends first, are not counted, so@P0is 1. - name - (optional, instead of index) the parameter's name, such as
@id, with or without the@.
The extracted value is text, as SQL Server Management Studio shows it: numbers in decimal, bit as 1 or 0, binary as 0x hex, dates in ISO 8601, and NULL as an empty value. When a transform writes a new value, it keeps the parameter's type, and a value that does not fit the type, such as letters for an int, is an error.
Example
Store the order id an sp_executesql lookup asks for, its first bound value, in a variable:
{
"filters": {"filters": [{"include": true, "detectedLocation": "FROM dbo.orders WHERE id", "operator": "CONTAINS"}]},
"extractor": {"type": "mssql_param", "config": {"index": "1"}},
"transforms": [{"type": "var_store", "config": {"name": "order_id"}}]
}
See Record SQL Server for mocking SQL Server.