动态SQL逗号分隔值查询

[Update: Using SQL Server 2005]

[更新:使用SQL Server 2005]

Hi, what I want to do is query my stored procedure with a comma-delimited list of values (ids) to retrieve rows of data.

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

The problem I am receiving is a conversion error:

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

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

The statement in my where-clause and error is:

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

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

Note: database.ID is of int type.

注意:database.ID是int类型。

I was following the article at:

我正在关注这篇文章:

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

but did not complete because of the error.

但由于错误没有完成。

In my execution script I have:

在我的执行脚本中,我有:

...
@PassedInIDs= '1,5'

Am I doing something wrong here? Thank you for your help.

我在这里做错了吗?谢谢您的帮助。

8 个解决方案

#1

I would strongly suggest that you use the second method from that link. Create a user-defined function that turns your comma-delimited string into a table, which you can then select from easily.

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

If you do a Google on Erland and "Dynamic SQL" he has a good writeup of the pitfalls that it entails.

如果你在Erland和“动态SQL”上做谷歌,他会很好地写出它所带来的陷阱。

#2

For one, you are passing a string to the IN function in SQL. If you look back at the original article, you'll see that instead of issuing a direct SQL statement, it instead is building a string which is the SQL statement to execute.

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

#3

I would create a CLR table-valued function:

我会创建一个CLR表值函数:

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

In it, you would parse the string apart and perform a conversion to a set of rows. You can then join on the results of that table, or use IN to see if an id is in the list.

在其中,您将分离字符串并执行到一组行的转换。然后,您可以加入该表的结果,或使用IN查看ID是否在列表中。

#4

You need to treat ufn_CSVToTable like it's a table. So you can join the function:

您需要将ufn_CSVToTable视为表格。所以你可以加入这个功能:

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

#5

I suggest using XML for this in SQL 2005. Somewhat bulkier, but it can be easier. It allows you to select the XML into a table which can then be joined or inserted etc.

我建议在SQL 2005中使用XML。这有点笨重,但它可以更容易。它允许您将XML选择到表中,然后可以将其连接或插入等。

Look at Sql Server's OPENXML() if you haven't already.

如果你还没有,请查看Sql Server的OPENXML()。

For example, you could pass in something like: '12...'

例如,您可以传递类似:'12 ......'的内容

and then use:

然后使用:

exec sp_xml_preparedocument @doc OUTPUT, @xmlParam

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

That should be a start

这应该是一个开始

#6

There is no string evaluation in SQL. This:

SQL中没有字符串评估。这个:

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

will not be turned to:

不会转向:

database.ID IN (1,2,3)

just because the @PassedInIDs parameter happens to contain '1,2,3'. The parameter is not even looked at, because all you have is a string containing " + @PassedInIDs + ". Syntactically, this is equivalent to:

只是因为@PassedInIDs参数碰巧包含'1,2,3'。甚至没有查看该参数,因为您拥有的只是一个包含“+ @PassedInIDs +”的字符串。从语法上讲,这相当于:

database.ID IN ('Bob')

To make it short, you can't do what you attempt here in SQL. But there are four other possibilities:

简而言之,您无法在SQL中执行此操作。但还有其他四种可能性:

you construct the SQL string in the calling language and abandon the stored procedure altogether

您使用调用语言构造SQL字符串并完全放弃存储过程

you use a dynamic prepared statement with as many parameters in the IN clause as you pan to use

在平移使用时,在IN子句中使用带有尽可能多参数的动态预准备语句

you use a fixed prepared statement with, say, 10 parameters: IN (?,?,?,?,?,?,?,?,?,?), filling only as many as you need, setting the others to NULL

你使用一个固定的预备语句,例如10个参数:IN(?,?,?,?,?,?,?,?,?,?),只填写你需要的数量,将其他参数设置为NULL

you create a stored procedure with, say, 10 parameters and pass in as many as you need, setting the others to NULL: IN (@p1, @p2, ..., @p10).

你创建一个存储过程,比如10个参数,并根据需要传入,将其他参数设置为NULL:IN(@ p1,@ p2,...,@ p10)。

#7

this may be solved by 6 ways as mentioned in Narayana's article Passing a list/array to an SQL Server stored procedure

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

And my most strait forward implementation is

而我最直接的实施是

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

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

#8

Here is what I have found and tested:

这是我发现和测试的内容:

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

Here is how function Call:

函数调用如何:

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

#1