Dynamic SQL Comma-Delimited Value Query

Question

[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.

Answer 1

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]

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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).

Answer 6

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.

Answer 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

-