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

Paul Cowan evolt at funkwit.com
Mon May 27 20:55:01 CDT 2002


Eric Engelmann <eric.engelmann at geonetric.com> wrote:
> 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.

Sorry Eric, no can do the way you've specified. Some DBMS will let you pass
"Array" parameters (and, indeed, I think ADO supports that) so you could
just have

CREATE PROCEDURE uspSearch
    @ProfileItemIDs array_of_ints_or_whatever

and what you write would work. But, in SQL, no deal (well.. SQL 7. I am
almost 100% certain it hasn't been added in 2000 though... though 2000 does
have "table variables" which are sort of similar, and might or might not be
useful).

What you can do is build an NVARCHAR inside the stored proc... so

    DECLARE @SQL NVARCHAR(4000)

    SET @SQL = 'SELECT [CompanyName] FROM [COMPANIES] WHERE [COMPANYID] IN
(' + @strCompanyIDList + ')'

    call sp_executesql @SQL

Note that doing this is obviously a lot messier, and may or may not totally
shaft your query performance due to recompilation issues etc.

Hope this helps!

Paul





More information about the thelist mailing list