使用“IN”的 SQL 语法问题具有动态数据的关键字

Question

我正在使用 SQL Server 2000，并且在 SQL 代码块方面遇到了一个奇怪的问题（如下）：
用户可以输入“GM”作为可能的参数或“F”。如果用户在存储过程查询字符串中输入“GM”作为参数，我需要 AutoDivision 包含 GMC、CAD、CHE、SAT

declare @AutoDivision as varchar(15) 
set @AutoDivision = 'GM' 

if @AutoDivision = 'GM' 
            Begin 
                Select @AutoDivision = '''Cad'', ''GMC'', ''Sat'', ''Che'''
            End 

            SELECT 
                 oh.OrderNumber, lg.[lgh_number]
            FROM 
                [dbo].[OrderHeader] oh (NOLOCK)
            INNER JOIN 
                [dbo].[DistrctHeader] lg (NOLOCK) ON oh.[inv_number] = lg.[inv_number]
            INNER JOIN
                [dbo].[DealerCompany] c (NOLOCK) ON c.cmp_id = LEFT(oh.[ordernumber],3)
            INNER JOIN
                [dbo].[divisionXREF] x (NOLOCK) ON x.Division = c.comp_revtype
            WHERE
                oh.ord_number = '113-889257'
                AND x.AutoDivision IN (@AutoDivision)
                --AND x.AutoDivision IN ('Cad', 'Sat', 'GMC', 'Che')
                AND lg.[lgh_outstatus] IN ('AVAIL', 'PLAN', 'DISP', 'STRTD', 'PEND','COMP')  

但是，当我运行下面的代码时，我没有取回任何记录。 当我取消注释代码行时
--AND x.AutoDivision IN ('Cad', 'Sat', 'GMC', 'Che')

它有效（我得到一条返回的记录）。

当我执行 print 'AND x.AutoDivision IN (' + cast(@AutoDivision as varchar) + ')'
我返回 AND x.AutoDivision IN ('Cad', 'GMC', 'Sat', 'Che')

我在这里错过了什么？

Answer 1

您不能使用单个变量来表示 SQL 中以逗号分隔的 IN 参数列表 - Oracle、MySQL、SQL Server...没关系。

要使此变量方法起作用，您需要使用动态 SQL，以便能够首先将查询创建为字符串（使用串联从变量中获取 IN 参数），然后执行查询语句：

DECLARE @cmd VARCHAR(1000)

SET @cmd = 'SELECT oh.OrderNumber, 
                   lg.[lgh_number]
              FROM [dbo].[OrderHeader] oh (NOLOCK)
              JOIN [dbo].[DistrctHeader] lg (NOLOCK) ON oh.[inv_number] = lg.[inv_number]
              JOIN [dbo].[DealerCompany] c (NOLOCK) ON c.cmp_id = LEFT(oh.[ordernumber],3)
              JOIN [dbo].[divisionXREF] x (NOLOCK) ON x.Division = c.comp_revtype
             WHERE oh.ord_number = '113-889257'
               AND x.AutoDivision IN ('+ @AutoDivision +')
               AND lg.[lgh_outstatus] IN (''AVL'', ''PLN'', ''DSP'', ''STD'', ''PND'',''CMP'')  '

EXEC(@cmd)

我建议阅读在实施动态 SQL 解决方案之前动态 SQL 的诅咒和祝福。

表值函数

表值函数将允许您在不使用动态 SQL 的情况下执行您想要的操作 - 有更多信息请参阅本文。

Answer 2

为了避免这种情况，您可以创建一个临时表，填充它，然后使用

IN (SELECT myField from #myTable)

Answer 3

即使您连接了看起来像是几个不同参数的内容，您的 IN 子句实际上是将其作为单个字符串进行测试（您确实将其声明为 varchar），并且没有记录与该谓词匹配也就不足为奇了。

看起来您正在尝试混合动态 sql 和标准查询。那是行不通的。您的查询要么必须全部动态创建，然后专门执行，要么您的 IN 子句必须输入单独的参数，这可以通过 subqery 完成。

您可以执行以下操作：

... AND x.AutoDivision IN 
(SELECT Division WHERE Corp = 'GM') ...

或者

... AND x.AutoDivision IN 
(SELECT 'Cad' UNION SELECT 'GMC' UNION SELECT 'Sat' UNION SELECT 'Che') ...

试试这个：

        SELECT 
             oh.OrderNumber, lg.[lgh_number]
        FROM 
            [dbo].[OrderHeader] oh (NOLOCK)
        INNER JOIN 
            [dbo].[DistrctHeader] lg (NOLOCK) ON oh.[inv_number] = lg.[inv_number]
        INNER JOIN
            [dbo].[DealerCompany] c (NOLOCK) ON c.cmp_id = LEFT(oh.[ordernumber],3)
        INNER JOIN
            [dbo].[divisionXREF] x (NOLOCK) ON x.Division = c.comp_revtype
        WHERE
            oh.ord_number = '113-889257'
            AND x.AutoDivision IN 
           (SELECT 'Cad' UNION SELECT 'GMC' UNION SELECT 'Sat' UNION SELECT 'Che')
            AND lg.[lgh_outstatus] IN ('AVL', 'PLN', 'DSP', 'STD', 'PND','CMP') 

这是一个内联子查询，它将任意字符串联合到结果集中。 （持保留态度。我离 Sql Server 界面还很远。）

Answer 4

   AND x.AutoDivision IN (@AutoDivision)
        --AND x.AutoDivision IN ('Cad', 'Sat', 'GMC', 'Che')

你能澄清一下这两行代码吗，它们不是在做同样的事情吗？

Answer 5

CREATE FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
    WITH Split(stpos,endpos) 
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
GO

DECLARE @AutoDivision varchar(50) = NULL
SET @AutoDivision ='Cad, Sat, GMC, Che'

SELECT * FROM divisionXREF P INNER JOIN dbo.SPLIT(ISNULL(@AutoDivision,''),',') as T ON P.lgh_outstatus = CASE T.strval WHEN '' THEN P.PG_CODE ELSE T.strval END