Posted by Tony Rogerson on 10/31/06 08:15

As per MC - varchar is 1 character.

In order to do IN (@Merchant) you will need to use dynamic SQL. the IN is
looking at a single value - the contents of @Merchant at the moment.

If you don't want to use dynamic SQL then split the string out into a set in
a table variable/temporary table and then you can join / use that in the IN.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials


"Andrew Tatum" <andrew.tatum@gmail.com> wrote in message
news:1162238426.301761.85480@m7g2000cwm.googlegroups.com...
> Alright, so I have this problem. I want to make it easy for me and
> others to be able to run a query and easily choose whether we want
> Merchants or NonMerchants. Previously, we would have to comment out
> bits of code and would leave things messy (it would also leave room for
> error). So, I'm thought DECLARE and SET would work. Wrong.
>
> This is what I have....
>
> DECLARE @Merchant VARCHAR
>
> SET @Merchant = (Select CONVERT(VARCHAR, Id) + ','
> FROM AdminAdvertiserTypesDDL
> WHERE Id IN (1,3,4,5)
> ) // Includes Active, Out of Business, Cease to do business, Inactive
>
> I've also tried...
>
> SET @Merchant = '1,3,4,5'
>
> Then, in the query itself I try:
>
> WHERE AdminAdvertiserTypesDDL.Id = @Merchant
> or
> WHERE AdminAdvertiserTypesDDL.Id IN @Merchant
> or
> WHERE AdminAdvertiserTypesDDL.Id IN (@Merchant)
> or
> WHERE AdminAdvertiserTypesDDL.Id LIKE @Merchant
>
> Either way, it will ONLY show me the merchants whose Id is 1. When I
> make the query:
>
> WHERE AdminAdvertiserTypesDDL.Id IN (1,3,4,5)
>
> I finally get the desired results.
>
> Any ideas or tips?
>
> Thank you so much!
>

[Back to original message]