5

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

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Oliver S
  • 621
  • 2
  • 9
  • 20
  • What SQL are you using? It shouldn't be trying to convert that value to an int. Can you show more of the code? – user54650 Feb 05 '09 at 17:58
  • SQL Server 2005? Offhand, that should be working. The error looks like it's treating ' + @PassedInIDs + ' as a string... are you missing a quote? – user54650 Feb 05 '09 at 18:02
  • lacking the entire query, I can't tell you. When you're using dynamic SQL, everything's quoted until you run exec on the quoted string. It shouldn't see ' + @PassedInIDs + ' as a literal string at any point. This looks to me like a case of the error appearing at the wrong piece of code. – user54650 Feb 05 '09 at 18:14
  • Show the whole query please - it looks like you are not building the dynamic sql string properly... – DJ. Feb 05 '09 at 18:21
  • casper... that edit you made MIGHT be changing a piece of literally-pasted code. – user54650 Feb 05 '09 at 21:07
  • possible duplicate of [Passing a varchar full of comma delimited values to a SQL Server IN function](http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) – bummi Nov 06 '14 at 10:40

9 Answers9

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.

Tom H
  • 46,766
  • 14
  • 87
  • 128
1

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.

hova
  • 2,811
  • 20
  • 19
  • The link he gave is of dyanamic SQL. He just has a typo somewhere in the syntax. We need the complete code to see what's up. – user54650 Feb 05 '09 at 18:50
  • The error he posted is what would happen if you did NOT use dynamic SQL. Since we don't have the full code, all bets are off. – hova Feb 05 '09 at 23:09
1

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).
Tomalak
  • 332,285
  • 67
  • 532
  • 628
  • He is trying to use Dynamic SQL. You can certainly pass "1,2,3" as a string, concatenate it into dynamic SQL, and be done with it. I do it all the time. You're incorrect, here. – user54650 Feb 05 '09 at 18:50
  • Then you have not read my answer well enough. He obviously thinks that he can do dynamic SQL *within* a stored procedure. Which you can't. At least not like this. – Tomalak Feb 05 '09 at 19:04
  • You certainly can. You save the subquery you want to use as a string, and execute. I don't personally like the injectability potential, but he wasn't asking for something that was perfectly secure. – user54650 Feb 05 '09 at 21:09
  • That's what I said: "At least not like this.". I know you can do dynamic SQL on the server using EXECUTE. But that's not what the question is about, and that's certainly nothing I would ever recommend doing in a stored procedure with parameters passed in "from the wild". – Tomalak Feb 06 '09 at 10:16
1

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.

casperOne
  • 73,706
  • 19
  • 184
  • 253
  • Or use Dynamic SQL, like he's trying to do? – user54650 Feb 05 '09 at 18:58
  • @cmartin: But he isn't, since this is in a stored procedure and he wants to get the comma-delimited list of values into the SP. One could use dynamic SQL in the SP, but that's just a waste. – casperOne Feb 05 '09 at 20:46
  • If you look at the link he's using, he's certainly trying to use Dynamic SQL. I don't find doing so a waste, either. We're looking at a query that might be 99% done, vs starting over and doing it a "better" way. – user54650 Feb 05 '09 at 21:08
0

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')
DareDevil
  • 5,249
  • 6
  • 50
  • 88
0

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]
david.mchonechase
  • 2,219
  • 3
  • 22
  • 24
  • This is completely unnecessary for the above query with dynamic SQL. the final string that evals will just be "IN (1,5)" which is valid SQL. – user54650 Feb 05 '09 at 18:16
0

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

Dan
  • 3,665
  • 1
  • 31
  • 39
  • @freelookenstein: Unfortunately, you incur a massive overhead with larger lists of values, in parsing, and generating the XML for the list. – casperOne Feb 05 '09 at 18:48
  • Why not just use Dynamic SQL, like he's trying? – user54650 Feb 05 '09 at 18:51
  • casperOne, what sort of significant overhead are you referring to? If you can link me to some documentation, I'd appreciate it. – Dan Feb 05 '09 at 18:55
  • XML Parsing is "significant overhead". At the very least, you go from parsing one string to language (the SQL) to two (sql and XML), nevermind any overhead from generating the XML itself. – user54650 Feb 05 '09 at 18:58
0

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

    -
Ahmed Mozaly
  • 1,454
  • 2
  • 15
  • 22
0
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)+',%';
Hong Van Vit
  • 2,884
  • 3
  • 18
  • 43