Dynamic Comma Separated SQL Query

Question

[Update: using SQL Server 2005]

Hi, I want to query my stored procedure using a comma-separated list of values ​​(ids) to retrieve rows of data.

The problem I get is a conversion error:

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

Statement in my where clause and error:

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

Note: database.ID is of type int.

I followed the article at:

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

but did not complete due to an error.

In my script execution, I have:

 ... @PassedInIDs= '1,5' 

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

Answer 1

I would highly recommend using the second method from this link. Create a custom function that turns your comma-separated string into a table that you can easily select.

If you do Google on Erland and Dynamic SQL, he has a good record of the errors that it entails.

Answer 2

First, you pass the string to the IN function in SQL. If you look at the original article, you will see that instead of issuing a direct SQL statement, a string is created instead, which is the SQL statement to execute.

Answer 3

I would create a CLR function:

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

In it, you parse the string separately and perform the conversion to a series of lines. You can then join the results of this table or use IN to find out if the identifier is in the list.

Answer 4

You need to consider ufn_CSVToTable as a table. So you can join the function:

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

Answer 5

I suggest using XML for this in SQL 2005. A little more cumbersome, but it might be easier. It allows you to select XML into a table, which can then be merged or inserted, etc.

Take a look at OPENXML Sql Server () if you haven't already.

For example, you can pass 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()') 

It must be the beginning

Answer 6

There is no string evaluation in SQL. It:

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

will not be addressed to:

 database.ID IN (1,2,3) 

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

 database.ID IN ('Bob') 

To do this briefly, you cannot do what you are trying here in SQL. But there are four more possibilities:

• you create a SQL string in the calling language and generally leave the stored procedure
• you use a dynamic prepared statement with the same number of parameters in the IN clause when you use pan to
• you use a fixed prepared statement with, say, 10 parameters: IN (?,?,?,?,?,?,?,?,?,?) , filling only as much as you need, setting the rest to NULL
• you create a stored procedure with, say, 10 parameters and pass as many as you need, setting the rest to NULL: IN (@p1, @p2, ..., @p10) .

Answer 7

this can be resolved in 6 ways, as described in the article Narayana Passing List / Array to SQL Server Stored Procedure

And my most breakthrough implementation is

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

• -

Answer 8

Here is what I 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 

This is how the Call function is:

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