[thelist] ASP/SQL: Parameter for IN () clause
Eric Engelmann
eric.engelmann at geonetric.com
Mon May 27 20:38:00 CDT 2002
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
More information about the thelist
mailing list