I have an application where the user will type in ID numbers into a single
field in an ASP page. They can type in multiple IDs if they are seperated
by a comma. I want to pass this string into a Stored Proc and just use an
"in" command in the where clause to return a recordset back to the ASP page.
The problem is that the ID numbers are integer and the parameter is a
varchar. When I try to pass multiple IDs into the Stored Proc I get an
error.
Here is the Stored Proc
CREATE PROCEDURE test @pvID as varchar(500) AS
Select * from member_t where member_ID in (@pvID)
GO
When I try it by typing - exec test '123456,123457,123458' - I get this
error
"Syntax error converting the varchar value '123456,123457,123458' to a
column of data type int."

How would I go about converting the string to an integer and still use the
In statement.
Thanks

Here is an excellent article:
http://www.sommarskog.se/dynamic_sql.html#List

--
Keith


"M Smith" <msmith (AT) avma (DOT) org> wrote

Also,

Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp



AMB

"M Smith" wrote:

Great article. It really helped a lot. Thank You.
Mike

"Keith Kratochvil" <sqlguy.back2u (AT) comcast (DOT) net> wrote

The real thanks should go to Erland for putting together such a great page!
Anyway, I am glad that I was able to help.

--
Keith


"M Smith" <msmith (AT) avma (DOT) org> wrote