[thelist] ASP/SQL: Parameter for IN () clause

[Eric Engelmann]

I'm hoping this is pretty easy:

I want to pass a string: "23,45,71,34,56" as a string parameter (I assume)
to a SQL stored procedure for use in an IN() statement. It works fine if I
construct the SQL Statement dynamically in ASP and execute it from there.
The problem is sending in a comma delimited string so the SQL 2000 sproc can
understand what it is and put it in the IN() statement.

The sproc looks something like this:

CREATE PROCEDURE uspSearch
	@strProfileItemIDs nvarchar(1000)
AS

SELECT
	[CompanyName]
FROM
	[COMPANIES]
WHERE
	[COMPANYID] IN (@strCompanyIDList)

And I'm calling it thusly:

	strSQL = "uspGetCompanyData " & strCompanyListID

Which of course won't work if you have a comma in there.

Also tried:

	strSQL = "uspGetCompanyData '" & strCompanyListID & "'"

Which gives me:

	Syntax error converting the nvarchar value
	'23,45,71,34,56' to a column of data type int.

I'm sure there's an easy way to pass this list into the IN statement via a
sproc parameter, but I'm not seeing it at the moment. Any ideas?

Thanks,

- E