I'm trying to run this query, but it fails it fails with
The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal.
select mt.pk from My_Table mt where json_value(mt.json_data, '$.group_id') != ''
As you can see, my parameter 2 is a string literal. The error is due a bug in SQL Server 2016, when
forced parameterization is on for the database.
I have confirmed that
forced parameterization is turned on in my database, using:
select name, is_parameterization_forced from sys.databases
I am generally aware that you can force simple parameterization by creating a plan guide, then using it for the query. However, reviewing this documentation, I think that may be too complicated for my actual situation. This is partly because in reality, these queries are be generated on the fly and it's not realistic to also generate plan guides for all of them.
Reviewing Query Hint docs, it says
The PARAMETERIZATION query hint can only be specified inside a plan guide. It cannot be specified directly within a query.
Upgrading to SQL Server 2017 is also not currently viable. Turning off
forced parameterization for these databases is also not viable.
I am thinking that if I can set
simple paramerization on for the query or session, that would be a workaround. Is such a technique possible? Is there any other workaround?
(PS: I realize that looking for solutions with simple parameterization is a little bit X/Y... any help is appreciated)