Posted by Erland Sommarskog on 06/09/05 00:41

Mahesh (gaware@gmail.com) writes:
> I am writing a SP in MSSQL. One of the parameted is VARCHAR type
> which have comma seperated INT vlaues. I want to use this varible in
> WHERE clause against INT type coloum. how do I achive this.
>
> eg. DECLARE @CompanyTypeIDs VARCHAR(200)
> SET @CompanyTypeIDs = '1,3,2'
>
> Currently I am using it as
> SELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN (1,2,3)
>
> CompanyTypeID is of INT type.
> But I want to replace the consts with the Parameter passed as below
>
> SELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN
> (@CompanyTypeIDs) - but this is not giving any result.

Using dynamic SQL as suggested by Stu is a possible solution, but is
one of the poorest ones.

Look at http://www.sommarskog.se/arrays-in-sql.html#iter-list-of-integers
for a quick solution. If you want to explore other solutions, read the
entire article (which is quite long).

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

• Next in forum: Re: How to Use Varchar with Int column
• Prev in forum: Re: When changing to type text from nvarchar, the 255 character limit is maintained?
• Thread view: Re: How to Use Varchar with Int column

[Reply to this message]