sql - 没有 sp_executesql 的动态 SQL

Question

这个问题的标题对于我所问的可能并不完全准确，但我真的不确定如何用一句话来概括它。但它的核心是我正在寻找某种方法来处理可以是特定值、选择语句或没有 sp_executesql 的空白字符串的输入。我正在为一个允许自定义 sql 但以奇怪的方式传递参数的系统编写报告。您通常应该做的是在应用程序中配置部分 where 子句，然后将其映射到令牌，即：#property#

当报告运行时，供应商的系统会获取您的 sql 语句，如果用户为映射到该标记的参数选择了一个值，则在运行之前将其插入到您的 sql 中。来自我们控制自己的用户界面应用程序的报告背景，这让我发疯，因为代码分散在各处，并且在整个报告定义中到处都是无效的 sql 行，我每次调试时都必须替换它们. 我正在尝试清理它并将所有令牌分配移动到我的代码顶部，在那里我将它们转储到我将在其余查询中使用的变量中。例如，应用程序中的报表配置设置 Condition50 = v.primaryKey in (#vendor#)。然后，每当对供应商进行查询过滤时，它看起来就像这样。

WHERE 1=1
#Condition50# --at runtime, #Condition50# is replaced with AND v.primaryKey in (#vendor#)

我的问题是，系统生成的某些令牌会根据用户选择参数值的方式以不同的格式传递。例如，如果用户明确选择了一些属性，那么应用程序会将我的#property# 标记替换为以下值：1,2,3（表示属性的主键，大多数过滤器似乎都以这种方式工作）。这意味着我可以将该标记用单引号括起来，并将其放入一个逗号分隔的列表中，我可以使用 CHARINDEX 进行过滤。即对于批准者：

WHERE (1 = 1)
AND (
    @selectedApprovers = ',,' OR
    CHARINDEX(',' + cast(x.approverhmy as varchar) + ',', @selectedApprovers, 0) > 0
)

在属性的情况下，用户还可以选择一组属性（属性列表）。当用户执行此操作时，#property# 标记将被替换为如下所示的 select 语句：select hProperty from listprop2 where hPropList in (1,2,3)。我无权访问 sp_executesql，因此我不能将过滤器转储到 varchar 中，检测何时提供特定 id 列表并使用它来生成有效的 sql 来填充表变量。如果我设置了这样的表（如下所示）并且用户没有选择列表，则 SQL 将变为无效：

文件中的 SQL

DECLARE @selectedProperties TABLE (hMy numeric)
IF '#hProp#' <> ''
BEGIN
    INSERT INTO @selectedProperties
    #hProp#
END

变为 - 具有特定属性

DECLARE @selectedProperties TABLE (hMy numeric)
IF '#hProp#' <> ''
BEGIN
    INSERT INTO @selectedProperties
    1,2,3
END

变为 - 选择了属性列表

DECLARE @selectedProperties TABLE (hMy numeric)
IF '#hProp#' <> ''
BEGIN
    INSERT INTO @selectedProperties
    select hProperty from listprop2 where hPropList in (1,2,3)
END

或者，如果未选择任何属性

DECLARE @selectedProperties TABLE (hMy numeric)
IF '#hProp#' <> ''
BEGIN
    INSERT INTO @selectedProperties
    --error, invalid sql
END

我越来越怀疑，对于这种过滤器类型，我将不得不接受它并忍受奇怪的行为，并继续在我的 SQL 中使用 #property# 标记乱扔 where 子句。我希望有人能想到一个奇怪的技巧，我可以用它来处理这两种情况，而不会导致 SQL 解析错误。我真的希望我能改变我们正在使用的应用程序中的一些设计决策。

标签： sqlsql-server