Forced Parameterization and CASE statements

Question

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.

Answer 1

If you are able to change the query text, using APPLY and VALUES will allow forced parameterization of the CASE literal:

SELECT
 CA.authorisation_amount
FROM authorisations AS A
CROSS APPLY
(
 VALUES
 (
 CASE 
 WHEN A.auth_date > '2019-02-03 08:48:03'
 THEN A.authorisation_amount 
 ELSE NULL
 END
 )
) AS CA (authorisation_amount)
WHERE
 A.auth_date > '2019-02-03 07:48:03'
 AND A.merchant_id = 400000000031;

But explicit parameterization is better. ORMs can be ok for simple uses, but a decent one will allow you to use your own stored procedures etc. as necessary.

• TimBrown: Great, non intuitive answer. I've verified locally: StatementText="select CA . authorisation_amount from authorisations as A cross APPLY ( values ( case when A . auth_date > @0 then A . authorisation_amount else null end ) ) as CA ( authorisation_amount) where A . auth_date > @1 and A . merchant_id = @2" No literals anywhere in the query!

Answer 2

So I should have done some more digging before I posted this. The restriction is explained deep down on this page: Forced Parameterization | Microsoft Docs

Specifically, this section:

The <select_list> of any SELECT statement. This includes SELECT lists of subqueries and SELECT lists inside INSERT statements.

This tells me it's not specifically the CASE, but that I have included literals in the SELECT list.

Still, if anyone has any suggestions for working around this, I'd be interested.