Passing comma seperated values to a stored proc

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: M Smith (msmith_at_avma.org)
Date: 12/22/04 

Date: Wed, 22 Dec 2004 10:39:06 -0600

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

Relevant Pages

  • RE: Passing comma seperated values to a stored proc
    ... > field in an ASP page. ... They can type in multiple IDs if they are seperated ... I want to pass this string into a Stored Proc and just use an ... > How would I go about converting the string to an integer and still use the ...
    (microsoft.public.sqlserver.programming)
  • Re: Passing comma seperated values to a stored proc
    ... >> field in an ASP page. ... They can type in multiple IDs if they are ... I want to pass this string into a Stored Proc and just use ...
    (microsoft.public.sqlserver.programming)
  • ASP ADO SP call returning closed recordset
    ... This was originally posted to comp.databases.ms-sqlserver, but no response ... Can anybody tell me why a) when running a stored proc from an asp page to ... 'execute the SP returning the result into a recordset ...
    (microsoft.public.sqlserver.programming)
  • Re: DTS Through ASP Page permissions
    ... I am running an asp page calling the stored proc. ... when I execute a package from a SQL Agent job using DTSRUN, ... you can capture your DTS execution in a log file. ...
    (microsoft.public.sqlserver.dts)
  • Re: sp_xml_removedocument cant find handle
    ... The handle lives until the connection is reset. ... stored proc and call that instead of doing this inside an ASP page by ... > Microsoft OLE DB Provider for SQL Server error '80040e14' ...
    (microsoft.public.sqlserver.xml)