如何从参数中获取ID列表，这些参数有时已经包含了ID，但有时还包括另一个sql查询。

Question

我在SSMS-2017中开发了一个SQL查询，如下所示：

DECLARE @property NVARCHAR(MAX) = @p;
SET @property = REPLACE(@property, '''', '');

DECLARE @propList TABLE (hproperty NUMERIC(18, 0));

IF CHARINDEX('SELECT', @property) > 0 OR CHARINDEX('select', @property) > 0
BEGIN
    INSERT INTO @propList
        EXECUTE sp_executesql @property;
END;
ELSE
BEGIN
    DECLARE @x TABLE (val NUMERIC(18, 0));

    INSERT INTO @x
        SELECT CONVERT(NUMERIC(18, 0), strval)
        FROM dbo.StringSplit(@property, ',');

    INSERT INTO @propList
        SELECT val
        FROM @x;
END;

SELECT ...columns...
FROM ...tables and joins...
WHERE ...filters...
  AND HMY IN (SELECT hproperty FROM @propList)

问题是，参数@p的值可能是ID列表(例如:1、2、3、4)或直接选择查询(例如:从mytable选择ID，其中代码为‘A 123’)。

代码运行良好，如上文所示。但是，它会在我们的系统中造成问题(因为我们使用Yadi7-Voyager)，而且我们只需要将select语句作为查询。为了管理它，我计划创建一个函数并在where子句中使用它，如：

WHERE HMY IN (SELECT myFunction(@p))

但是，我无法管理它，因为我看到不能在SQL函数中执行动态查询。那我就堆满了。在这一点上，任何处理这一问题的想法都将不胜感激。

Answer 1

其他人指出，解决这一问题的最佳办法是改变设计，我同意他们的意见。不过，我也想把您的问题当作学术问题来处理，如果将来的读者在设计更改不可能/不需要的情况下有相同的问题，我也会这样回答。

我可以想到两种方法，您可以在一个选择中尝试做什么，只要没有其他限制您可以做什么，您还没有提到。为了保持简短，我只想给您提供一些psuedo代码，这些代码可以适应您的情况，也可以适应未来读者的情况：

1. OPENQUERY (或开放)

您可以将上面的代码合并到存储过程中，而不是函数中，因为存储过程确实允许动态sql，而不是函数。然后应用程序中的SELECT查询将是一个从OPENQUERY中选择(执行存储的Prodedure)。

1. 联合所有可能性。

我确信没有人会想要使用它，但我提到它在学术上和我所知道的一样完整。

第二种可能只有在应用程序可能支持的有限的、已知的查询数的情况下才能工作。例如，您只能从Properties (由column1过滤)或TableB (由Column2和/或Column3过滤)获取您的TableB。

可能比这些可能性更多，但它必须是一个有限的，已知的数量，并且越多的可能性，代码将变得越复杂和冗长。

但是如果是这样的话，您可以简单地从一个UNION中选择所有可能的场景，并使其只返回UNION中的一个选择结果。

例如：

SELECT ... FROM TableA WHERE Column1=fnGetValue(@p, 'Column1')
AND CHARINDEX('SELECT', @property) > 0
AND CHARINDEX('TableA', @property) > 0
AND CHARINDEX('Column1', @property) > 0
AND (Whatever other filters are needed to uniquely identify this case)
UNION ALL 
SELECT
...

请注意，fnGetValue()不是内置函数。你得写下来。它将解析@p中的字符串，查找‘Column1 1=’的位置，并返回它后面的任何值。

在UNION ALL的末尾，您需要向查询中添加最后一个UNION，该查询将处理用户传递逗号分隔字符串而不是查询的情况，但这很容易，因为在代码中填充表变量的所有步骤都是不必要的。您可以简单地执行以下最后的查询：

WHERE NOT CHARINDEX('SELECT', @p) > 0
AND HMY IN (SELECT strval FROM dbo.StringSplit(@p, ','))

我非常肯定，这种可能性比它的价值要大得多，但它是一个示例，说明了一般情况下，如何将动态SQL替换为常规SQL，它简单地涵盖了您希望动态sql能够处理的所有可能选项。