Dynamic SQL Comma-Delimited Value Query

问题:

[Update: Using 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.

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:

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

Note: database.ID is of int type.

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.

回答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.

回答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.

回答3:

I would create a CLR table-valued function:

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.

回答4:

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

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.

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

For example, you could pass in something like: '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:

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:

database.ID IN ('Bob')

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

1. you construct the SQL string in the calling language and abandon the stored procedure altogether
2. you use a dynamic prepared statement with as many parameters in the IN clause as you pan to use
3. you use a fixed prepared statement with, say, 10 parameters: IN (?,?,?,?,?,?,?,?,?,?), filling only as many as you need, setting the others to NULL
4. 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).

回答7:

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

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

-

回答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')