动态 SQL 逗号分隔值查询

Question

[更新：使用 SQL Server 2005]

您好，我想要做的是使用逗号分隔的值列表 (ids) 查询我的存储过程以检索数据行。

我收到的问题是转换错误：

Conversion failed when converting the varchar value ' +
@PassedInIDs + ' to data type int.

我的 where 子句和错误中的语句是：

...
AND (database.ID IN (' + @PassedInIDs + '))

注意：database.ID 是 int 类型。

我正在关注这篇文章：

http://www .sql-server-helper.com/functions/comma-delimited-to-table.aspx

但由于错误而未完成。

在我的执行脚本中，我有：

...
@PassedInIDs= '1,5'

我在这里做错了什么吗？ 感谢您的帮助。

Answer 1

我强烈建议您使用该链接中的第二种方法。 创建一个用户定义的函数，将逗号分隔的字符串转换为表格，然后您可以轻松地从中进行选择。

如果你用 Google 搜索 Erland 和“Dynamic SQL”，他对其中的陷阱有很好的描述。

Answer 2

其一，您将一个字符串传递给 SQL 中的 IN 函数。 如果您回顾一下原始文章，您会发现它不是发出直接的 SQL 语句，而是构建一个字符串，它是要执行的 SQL 语句。

Answer 3

SQL 中没有字符串计算。 这个：

database.ID IN (' + @PassedInIDs + ')

变成：：

database.ID IN (1,2,3)

不会仅仅因为@PassedInIDs参数恰好包含'1,2,3'而 。 甚至没有查看该参数，因为您拥有的只是一个包含 " + @PassedInIDs + " 的字符串。 从语法上讲，这相当于：

database.ID IN ('Bob')

简而言之，您无法在 SQL 中执行您在此处尝试的操作。 但还有其他四种可能性：

1. 您用调用语言构造 SQL 字符串并完全放弃存储过程
2. 您使用动态准备语句，其 IN 子句中的参数与您平移使用的参数一样多
3. 您使用固定准备语句，例如，10个参数：IN(?,?,?,?,?,?,?,?,?,?)，只填你需要的数量，将其他设置为NULL
4. 你创建了一个存储过程包含 10 个参数，并传入所需数量的参数，将其他参数设置为 NULL：IN (@p1, @p2, ..., @p10)。

Answer 4

我将创建一个 CLR 表值函数：

http://msdn.microsoft。 com/en-us/library/ms131103.aspx

在其中，您将解析字符串并执行到一组行的转换。 然后，您可以连接该表的结果，或使用 IN 来查看某个 id 是否在列表中。

Answer 5

您需要像对待表格一样对待 ufn_CSVToTable。 所以你可以加入这个函数：

JOIN ufn_CSVToTable(@PassedInIDs) uf ON database.ID = uf.[String]

Answer 6

我建议在 SQL 2005 中使用 XML 来实现此目的。有点庞大，但它可以更容易。 它允许您将 XML 选择到表中，然后可以连接或插入等。

如果您还没有看过，请查看 Sql Server 的 OPENXML()。

例如，您可以传递类似以下内容：
'12...'

然后使用：

exec sp_xml_preparedocument @doc OUTPUT, @xmlParam

SELECT element 
FROM OPENXML (@doc, 'Array/Value', 2) WITH (element varchar(max) 'text()')

这应该是一个开始

Answer 7

这可以通过 Narayana 的文章中提到的 6 种方法来解决 将列表/数组传递给 SQL Server 存储过程< /a>

我最困难的实现是

声明 @statement nvarchar(256)
set @statement = 'select * from Persons where Persons.id in ('+ @PassedInIDs +')'
exec sp_executesql @statement

-

Answer 8

这是我发现和测试的：

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE  FUNCTION [dbo].[SplitStrings] ( @IDsList VARCHAR(MAX) )
RETURNS @IDsTable TABLE ( [ID] VARCHAR(MAX) )
AS 
BEGIN
    DECLARE @ID VARCHAR(MAX)
    DECLARE @Pos VARCHAR(MAX)

    SET @IDsList = LTRIM(RTRIM(@IDsList)) + ','
    SET @Pos = CHARINDEX(',', @IDsList, 1)

    IF REPLACE(@IDsList, ',', '') <> '' 
        BEGIN
            WHILE @Pos > 0 
                BEGIN
                    SET @ID = LTRIM(RTRIM(LEFT(@IDsList, @Pos - 1)))
                    IF @ID <> '' 
                        BEGIN
                            INSERT  INTO @IDsTable
                                    ( [ID] )
                            VALUES  ( CAST(@ID AS VARCHAR) )
                        END
                    SET @IDsList = RIGHT(@IDsList, LEN(@IDsList) - @Pos)
                    SET @Pos = CHARINDEX(',', @IDsList, 1)
                END
        END 
    RETURN
END

GO

这是函数调用的方式：

SELECT * FROM dbo.SplitStrings('123,548,198,547,965')

Answer 9

Try this:

    DECLARE @Ids varchar(50);
    SET @Ids = '1,2,3,5,4,6,7,98,234';
    
    SELECT * 
    FROM sometable 
    WHERE ','+@Ids+',' LIKE '%,'+CONVERT(VARCHAR(50),tableid)+',%';