sp_describe_undeclared_parameters cannot determine undeclared parameter in sqlxml

Question

this works fine:

sp_describe_first_result_set @tsql = N'
select
    *
from TestTable0
where
    id = @Id
'

the following is not:

execute sp_describe_undeclared_parameters @tsql = N'

select
    *
from TestTable0
where
    myxmlcolumn.exist(''/a[. = sql:variable("@a")]'') = 1

'

XQuery: Unable to resolve sql:variable('@a'). The variable must be declared as a scalar TSQL variable.

if the user wants to determine undeclared parameters in general case, this point will add a complications

if undeclared parameters in the sqlxml is out of the scope of sp_describe_undeclared_parameters, then it would be better to highlight this in the appropriate MSDN article.

it's not a question, looks like a imperfection in the sp_describe_undeclared_parameters or in MSDN article. Sorry if I posted it at a wrong place, can someone transfer it to the right place?

thank you.

Answer 1

In each article in the SQL Server Docs (and other Microsoft Learn), there is a pen icon in the upper-right corner. with the tool-tip "Edit this document". Clicking this icon takes you to the Github repository. Rather than actually editing the document, you can opt to file an issue for the topic. I would recommend that you do the latter, and leave it to the writers to decide where they want to put this restriction. (Because it is obviously a restriction. Wresting out the @a from the XML string appears non-trivial. Even more so deducing a suitable data type for it.)

You will need to have a Github account. When filing an issue, include a link to the topic for sp_describe_undeclared_parameters, as the list of issues applies to the entire repository.

If you feel strongly that this restriction should actually be lifted, the place to go to is https://feedback.azure.com/forums/908035-sql-server. When filing a suggestion it certainly helps if you can state your business case. That is, what the impact of the restriction is etc. This is something Microsoft gives a lot of weight to. But as I noted above, I am not sure that this is doable at all, at least for untyped xml.

Answer 2

thank for your answer, Erland. I noticed the pen icon is exists only in en-us version of the doc, but I read ru-ru. anyway, I created issue about the doc at github.