主页 / dba / 问题 / 231331
Accepted
TimBrown
Asked: 2019-03-06 04:24:28 +0800 CST

强制参数化和 CASE 语句

  • 772

我们有一个使用 ORM 的应用程序，它不（容易）支持参数化查询。为了解决这个问题，我在我们的数据库 (SQL2008 R2) 上启用了强制参数化以避免缓存膨胀，并鼓励计划重用。

一个更复杂的查询没有被完全参数化，我已经将其缩小到 CASE 语句中使用的文字值：

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

（这个查询可能看起来不直观，但它是一组更大的嵌套子查询的片段，我们必须在最底层使用这个结构。这作为一个例子。）

查询计划使用的参数化语句为：

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"

您会注意到 WHERE 子句中的文字已被参数化，但 CASE 语句中的日期文字尚未参数化。

为什么是这样？我错过了什么？我可以尝试解决这个问题吗？每次执行都会更改文字，所以目前，即使强制参数化，我最终也会为每次执行制定一个新计划。

sql-server sql-server-2008-r2

2 个回答

  1. 所以我应该在发布之前做更多的挖掘工作。该限制在此页面的深处进行了解释： Forced Parameterization | 微软文档

    具体来说，本节：

    任何 SELECT 语句的 <select_list>。这包括子查询的 SELECT 列表和 INSERT 语句中的 SELECT 列表。

    这告诉我这不是具体的 CASE，而是我在 SELECT 列表中包含了文字。

    不过，如果有人对解决此问题有任何建议，我会很感兴趣。

    • 1
  2. Best Answer

    如果您能够更改查询文本，则使用APPLY和VALUES将允许对CASE文字进行强制参数化：

    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;

    但是显式参数化更好。ORM 可以用于简单用途，但一个体面的 ORM 将允许您根据需要使用自己的存储过程等。

    • 1

相关问题