We have an application which uses an ORM which does not (easily) support parameterized queries. To combat this, I have enabled Forced Parameterization on our database (SQL2008 R2) in order to avoid cache bloat, and encourage plan re-use.
One of the more complex queries is not being completely parameterized, and I have narrowed it down to literal values being used in CASE statements:
SELECT CASE WHEN auth_date > '2019-02-03 08:48:03' THEN authorisations.authorisation_amount END FROM authorisations WHERE auth_date > '2019-02-03 07:48:03' AND merchant_id = 400000000031
(This query may not look intuitive, but it is a fragment of a larger set of nested subqueries, and we have to use this structure at the lowest level. This works as an example.)
The parameterized statement used by the query plan is:
StatementText="select case when auth_date > '2019-02-03 08:48:03' then authorisations . authorisation_amount end from authorisations where auth_date > @0 and merchant_id = @1"
You'll notice that the literals in the WHERE clause have been parameterized, but the date literal in the CASE statement has not.
Why is this? What am I missing? What could I try to fix this? The literals will change for every execution, so at the moment, even with forced parameterization, I will end up with a new plan per execution.