show_dynamic_params_in_xml

Description

Returns parameter information for a dynamic SQL query (a prepared statement) in XML format.

Syntax

show_dynamic_params_in_xml(object_id)

Parameters

object_id: ID of the dynamic, SQL lightweight stored procedure you are investigating. Usually the return value of the @@plwpid global variable.

Examples

Example 1

For this example, first find the object ID:

select @@plwpid

----------------
707749902

Then use the ID as the input parameter for show_dynamic_params_in_xml:

select show_dynamic_params_in_xml(707749902)

<?xml version="1.0" encoding="UTF-8"?>
<query>
    <parameter>
        <number>1</number>
        <type>INT</type>
        <column>tab.col1</column>
    </parameter>
</query>

Parameter	Value	Definition
`number`	`1`	Dynamic parameter is in the statement’s first position
`type`	`INT`	Table uses the int datatype
`column`	`tab.col1`	Query use the col1 column of the tab table

Usage

show_dynamic_params_in_xml allows dynamic parameters in where clauses, the set clause of an update, and the values list of an insert.
For where clauses, show_dynamic_params_in_xml determines associations according to the smallest subtree involving an expression with a column, a relational operator, and an expression with a parameter. For example:
```
select * from tab where col1 + 1 = ?
```
If the query has no subtree, show_dynamic_params_in_xml omits the <column> element. For example:
```
select * from tab where ? < 1000
```
show_dynamic_params_in_xml selects the first column it encounters for expressions involving multiple columns:
```
delete tab where col1 + col2 > ?
```
The association is unambigous for update . . . set statements. For example:
```
update tab set col1 = ?
```