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

Here goes for the string split...

create a sequence table (just numbers)

create table Seq (
rownumber int not null primary key clustered
)

set nocount on

declare @i int
set @i = 1
while @i <= 1000
begin
insert seq ( rownumber ) values( @i )
set @i = @i + 1

end

Now you can split the string

DECLARE @id_from_multiselect_list_box varchar(100)
SET @id_from_multiselect_list_box = ',11,22,33,44,55,' -- note
requirement for ',' at start and end of string.

DECLARE @merchant_id table ( id int not null )
insert @merchant_id ( id )
select id = substring( @id_from_multiselect_list_box, s.rownumber+1,
s2.rownumber-1 )
from seq s
inner join seq s2 on s2.rownumber = charindex( ',', substring(
@id_from_multiselect_list_box,
s.rownumber+1,
len(
@id_from_multiselect_list_box ) ) )
where substring( @id_from_multiselect_list_box, s.rownumber, 1 ) = ','

Now you can do this...

SELECT [Merchant],
[University],
CONVERT(Varchar, [Month]) + '/' + CONVERT(Varchar, [Year])
[SendOn],
SUM([Total Scheduled]) [Total Scheduled],
SUM([Total Rejected]) [Total Rejected],
SUM([Toal Received]) [Toal Received],
[Enrollment Message?]
FROM (
SELECT
MessageInfo.Advertiser [Merchant],
MessageInfo.Name [University],
DATEPART(month, MessageInfo.SendOn) [Month],
DATEPART(year, MessageInfo.SendOn) [Year],
MessageInfo.Total [Total Scheduled],
MessageInfo.Refused [total rejected],
(MessageInfo.Total - MessageInfo.Refused) [Toal Received],
CASE WHEN AdMessageRequests.IsEnrollmentMessage = 1 THEN
'Yes'
ELSE 'No'
END [Enrollment Message?]
FROM MessageInfo
JOIN AdMessageRequests ON AdMessageRequests.MessageId =
MessageInfo.id
JOIN Advertisers ON Advertisers.id = MessageInfo.AdvertiserId
JOIN AdvertiserTypes ON Advertisers.AdvertiserTypeId =
AdvertiserTypes.Id
WHERE MessageInfo.SendON < @startdate
AND Advertisers.AdvertiserTypeId IN ( SELECT id FROM
@merchant_id )
) mess

GROUP By [Merchant], [University], mess.[Month], mess.[Year],
[Enrollment Message?]
ORDER BY [Merchant], [University], mess.[Year], mess.[Month],
[Enrollment Message?]



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


"Tony Rogerson" <tonyrogerson@sqlserverfaq.com> wrote in message
news:ei70q8$st4$1$830fa7a5@news.demon.co.uk...
> 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]