>How do I fix this ??
>Syntax error converting the varchar value '1, 2, 3, 4, 5' to a column
>of data type int.
>CREATE PROCEDURE my_sp AS
This is a FAQ.
The In() construct expects a list, either a list of literal values
by commas, or a list of variables, separated by commas. The query
_will not_ parse a variable passed at this point to see if it contains
list of values. One reason: the mere presence of commas in a variable
not necessarily mean the variable contains a list of values. Don't you
any character fields containing data that includes commas in your
Why should the query engine know in one case that you're attempting to
a list of values, and in another, you're passing the actual data
all) that you want it to find in the column? Remember, at compile
query engine has no idea what that variable will contain. It _can't _
expected to know that it has to parse the future contents of the
just because it's the only variable appearing in the In() construct.
There are two ways to accomplish what you're after:
1) use charindex to search for the values:
Of course, this will force a table scan, but performance may be
adequate for your needs. You will also need to code delimiters into
the variable and the statement, to keep '4' from being found in this
You .can use any delimiter you want
2) dynamic SQL:
you run it, but the resulting impact on performance is not likely to
great in SQL7 as it was 6.5. Again - you will need to test both
see which one better suits your needs.
Here's a third way (only works with SS2000 and above) - thanks to
> you could use xml, too:
> create table tblExample(intPK int)
> insert tblExample values(3)
> insert tblExample values(56)
> insert tblExample values(34)
> insert tblExample values(300)
> insert tblExample values(301)
> create proc [tmp] as begin
> select * from tblExample
> where intPK in(
> select v from openxml(
> with(v int)
> exec tmp
> drop procedure tmp
> drop table tblExample
For SQL 2000, you can use a UDF (thanks to Marc Litchfield:
pretty easy to write a UDF to parse a comma-delimited string and
table variable (this is called a table-valued function), so you
could do something like this:
FROM tblExample ex
Which would return the position of the value of ex.intPK within the
[---- Begin SQL ----]
-- Function: udfSplitInt
-- Description: Returns a table variable from a string containing a
-- delimited list of integers
-- Author: Marc Litchfield, 09/05/01
CREATE FUNCTION udfSplitInt
IdentityID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
Element int NULL )
[---- End SQL ----]
Thanks to Michael Walsh, here's yet another way:
AccountID + ',%' )
note that there is no space after the comas.
It works simply. If AccountID is 45, clearly ',1,4,5,7,' LIKE
'%,45,%' returns false.
If AccountID is 4, on the other hand, ',1,4,5,7,' LIKE
So, you have, in effect, an IN( ) where the list is a parameter.
Please reply to the newsgroup. My reply-to address is my "spam trap" and I don't check it very often.