How to pass a list of integers to a stored procedure ?

12 Aug 2006 7:48 PM

fniles

colA is a an INT column
I need to create a stored procedure that has a parameter of list of values
for colA.
For example, 1,2,3
When I do the following, I got an error "Syntax error converting the varchar
value '1,2,3' to a column of data type int."

declare @m_EEIs varchar(50)
set @m_EEIs = '1,2,3'
select colA, colB
from tblA where colA in (@m_EEIs)

How can I pass a list of values (a list of integers) to the stored procedure
and not getting the error ?
Thank you.

12 Aug 2006 8:02 PM

Andrew J. Kelly

Have a look here: http://www.sommarskog.se/arrays-in-sql.html

--
Andrew J. Kelly SQL MVP

Show quote

"fniles" <fni***@pfmail.com> wrote in message
news:unMXNhkvGHA.1772@TK2MSFTNGP06.phx.gbl...
> colA is a an INT column
> I need to create a stored procedure that has a parameter of list of values
> for colA.
> For example, 1,2,3
> When I do the following, I got an error "Syntax error converting the
> varchar value '1,2,3' to a column of data type int."
>
> declare @m_EEIs varchar(50)
> set @m_EEIs = '1,2,3'
> select colA, colB
> from tblA where colA in (@m_EEIs)
>
> How can I pass a list of values (a list of integers) to the stored
> procedure and not getting the error ?
> Thank you.
>
>
>

12 Aug 2006 8:09 PM

ML

Dejan Sarka wrote a post on this subject and included a function, but at this
very moment the Solid Quality Learning site doesn't seem to be online.

Look for the post here:
http://solidqualitylearning.com/blogs/Dejan/

I think it was posted in October 2005.

ML

---
http://milambda.blogspot.com/