Need to declare multiple values

[Andrew Tatum]

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 AdminAdvertiser TypesDDL
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 AdminAdvertiser TypesDDL.Id = @Merchant
or
WHERE AdminAdvertiser TypesDDL.Id IN @Merchant
or
WHERE AdminAdvertiser TypesDDL.Id IN (@Merchant)
or
WHERE AdminAdvertiser TypesDDL.Id LIKE @Merchant

Either way, it will ONLY show me the merchants whose Id is 1. When I
make the query:

WHERE AdminAdvertiser TypesDDL.Id IN (1,3,4,5)

I finally get the desired results.

Any ideas or tips?

Thank you so much!

[MC]

The problem is in the variable declaration. You stated DECLARE @Merchant
VARCHAR
which basically means varchar(1). That means your '1,2,3,4' got trimmed to
the '1'.
Try putting DECLARE @Merchant VARCHAR(100) or something like that.
MC

"Andrew Tatum" <an**********@g mail.comwrote in message
news:11******** *************@m 7g2000cwm.googl egroups.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 AdminAdvertiser TypesDDL
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 AdminAdvertiser TypesDDL.Id = @Merchant
or
WHERE AdminAdvertiser TypesDDL.Id IN @Merchant
or
WHERE AdminAdvertiser TypesDDL.Id IN (@Merchant)
or
WHERE AdminAdvertiser TypesDDL.Id LIKE @Merchant

Either way, it will ONLY show me the merchants whose Id is 1. When I
make the query:

WHERE AdminAdvertiser TypesDDL.Id IN (1,3,4,5)

I finally get the desired results.

Any ideas or tips?

Thank you so much!

[Andrew Tatum]

Thanks MC!

However, I now get this error: Syntax error converting the varchar
value '1,3,4,5' to a column of data type int.

Any ideas?

[--CELKO--]

1) start using the standard CAST() and not the proprietary CONVERT().

2) There is no such thing as a gernal purpose magical universal "id",
so your DDL that you did not post will make no sense as a data model.
The names are all wrong. A table cannot have a type -- that is an
attribute. The DDL is where the table is created, not a kind of table.
3) A SELECT statement has only one WHERE clause, not the OR-ed chain
you showed. What you wanted was something like this, assuming that
AdminAdvertiser s are logically different from mere Advertisers.

SELECT <col list>
FROM AdminAdvertiser s
WHERE foobar_type IN (1,2,3,4);

4) Syntax error converting the varchar value '1,3,4,5' to a column of
data type int. <<

Of course! Look at the string; it is full of commas so it cannot be
parsed as an INTEGER.

Your problem is that you do not understand that a column is always a
scalar value. You have no idea how to create a data model and a
newsgroup is not going to give you that kind of training.

See if you can get help from some who knows SQL and data modeling for a
few months. Your mental model is wrong.

[Andrew Tatum]

Sorry that I do not use CAST. Convert works for the time being. I don't
consider myself to be "advanced" by any means, however, I don't think I
need some "basic training." Here is a look at the entire query, as it
may help you understand it easier.

DECLARE @startdate smalldatetime, @Merchant varchar, @NonMerchant
varchar

SET @startdate = '10/01/2006'; // Start Date
SET @Merchant = '1,3,4,5'; // Includes Active, Out of Business, Cease
to do business, Inactive
SET @NonMerchant = '2,6'; // Includes Inhouse, School Messengers

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.Adv ertiser [Merchant],
MessageInfo.Nam e [University],
DATEPART(month, MessageInfo.Sen dOn) [Month],
DATEPART(year, MessageInfo.Sen dOn) [Year],
MessageInfo.Tot al [Total Scheduled],
MessageInfo.Ref used [total rejected],
(MessageInfo.To tal - MessageInfo.Ref used) [Toal Received],
CASE WHEN AdMessageReques ts.IsEnrollment Message = 1 THEN
'Yes'
ELSE 'No'
END [Enrollment Message?]
FROM MessageInfo
JOIN AdMessageReques ts ON AdMessageReques ts.MessageId =
MessageInfo.id
JOIN Advertisers ON Advertisers.id = MessageInfo.Adv ertiserId
JOIN AdvertiserTypes ON Advertisers.Adv ertiserTypeId =
AdvertiserTypes .Id
WHERE MessageInfo.Sen dON < @startdate
AND Advertisers.Adv ertiserTypeId IN @Merchant
) mess

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

Basically, I am trying to avoid this:

WHERE AdminAdvertiser List.Advertiser TypeId IN (
1, // (Name: Active)
// 2, // (Name: In-House)
3, // (Name: Out of Business)
4, // (Name: Cease to do Business)
5 // (Name: Inactive)
// 6, // (Name: School Messenger)
)

I have to comment things out all the time. It really makes no sense...
and it causes a lot of human error.

[Tony Rogerson]

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" <an**********@g mail.comwrote in message
news:11******** *************@m 7g2000cwm.googl egroups.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 AdminAdvertiser TypesDDL
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 AdminAdvertiser TypesDDL.Id = @Merchant
or
WHERE AdminAdvertiser TypesDDL.Id IN @Merchant
or
WHERE AdminAdvertiser TypesDDL.Id IN (@Merchant)
or
WHERE AdminAdvertiser TypesDDL.Id LIKE @Merchant

Either way, it will ONLY show me the merchants whose Id is 1. When I
make the query:

WHERE AdminAdvertiser TypesDDL.Id IN (1,3,4,5)

I finally get the desired results.

Any ideas or tips?

Thank you so much!

[Tony Rogerson]

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_multis elect_list_box varchar(100)
SET @id_from_multis elect_list_box = ',11,22,33,44,5 5,' -- 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_multis elect_list_box, s.rownumber+1,
s2.rownumber-1 )
from seq s
inner join seq s2 on s2.rownumber = charindex( ',', substring(
@id_from_multis elect_list_box,
s.rownumber+1,
len(
@id_from_multis elect_list_box ) ) )
where substring( @id_from_multis elect_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.Adv ertiser [Merchant],
MessageInfo.Nam e [University],
DATEPART(month, MessageInfo.Sen dOn) [Month],
DATEPART(year, MessageInfo.Sen dOn) [Year],
MessageInfo.Tot al [Total Scheduled],
MessageInfo.Ref used [total rejected],
(MessageInfo.To tal - MessageInfo.Ref used) [Toal Received],
CASE WHEN AdMessageReques ts.IsEnrollment Message = 1 THEN
'Yes'
ELSE 'No'
END [Enrollment Message?]
FROM MessageInfo
JOIN AdMessageReques ts ON AdMessageReques ts.MessageId =
MessageInfo.id
JOIN Advertisers ON Advertisers.id = MessageInfo.Adv ertiserId
JOIN AdvertiserTypes ON Advertisers.Adv ertiserTypeId =
AdvertiserTypes .Id
WHERE MessageInfo.Sen dON < @startdate
AND Advertisers.Adv ertiserTypeId 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" <to**********@s qlserverfaq.com wrote in message
news:ei******** ***********@new s.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" <an**********@g mail.comwrote in message
news:11******** *************@m 7g2000cwm.googl egroups.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 AdminAdvertiser TypesDDL
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 AdminAdvertiser TypesDDL.Id = @Merchant
or
WHERE AdminAdvertiser TypesDDL.Id IN @Merchant
or
WHERE AdminAdvertiser TypesDDL.Id IN (@Merchant)
or
WHERE AdminAdvertiser TypesDDL.Id LIKE @Merchant

Either way, it will ONLY show me the merchants whose Id is 1. When I
make the query:

WHERE AdminAdvertiser TypesDDL.Id IN (1,3,4,5)

I finally get the desired results.

Any ideas or tips?

Thank you so much!

[Hugo Kornelis]

On 30 Oct 2006 13:45:01 -0800, Andrew Tatum wrote:

(snip)

Here is a look at the entire query, as it
may help you understand it easier.

Hi Andrew,

Some comments inline and after the query.

>DECLARE @startdate smalldatetime, @Merchant varchar, @NonMerchant
varchar

Default length for varchar is 1. You'll probably want to include the
length in the declaration.

>SET @startdate = '10/01/2006'; // Start Date

Depending on language settings, @startdate might now be either january
10 or october 1. Use the yyyymmdd format ('20061001' or '20060110'),
that format is guaranteed to be unambiguous.

>SET @Merchant = '1,3,4,5'; // Includes Active, Out of Business, Cease
to do business, Inactive
SET @NonMerchant = '2,6'; // Includes Inhouse, School Messengers

Due to default length of varchar declaration, these variables will now
be equal to '1' and '2', resepctively.

(snip)

AND Advertisers.Adv ertiserTypeId IN @Merchant

That would result in a syntax error. I assume that the actual query has
parentheses around @Merchant:
AND Advertisers.Adv ertiserTypeId IN (@Merchant)

With the current code, this equates to
AdvertiserTypeI D IN ('1')
which will return only type 1. But adding a length to the declaration
won't fix this - with that chage, you'll have
AdvertiserTypeI D IN ('1,3,4,5')
instead of what you really need, which is one of these two
AdvertiserTypeI D IN ('1','3','4','5 ')
AdvertiserTypeI D IN (1,3,4,5)
(Which one you need depends on the datatype of the AdvertiserTypeI D
column)

Tony already suggested the use of dynamic SQL or a string splitter. If
you decide to use one of those techniques, then I suggest that you first
read Erland Sommarskog's excellent articles on these two techniques:
- http://www.sommarskog.se/dynamic_sql.html
- http://www.sommarskog.se/arrays-in-sql.html

However, I think that neither is the best solution in this case. From
your descriptions in this thread so far, I gather that you have
advertisers of different types, and that some types (1, 3, 4, and 5) are
considered to be merchants and other types (2 and 6) are non-merchants.

What will you do if a change in business requirements forces you to add
a new advertiser type (7). Change all your code and pray that you don't
miss a place where the lists of merchant vs non-merchant types is
included in a query?

Instead of hardcoding information in your code, why not store it in the
place that is designed to hold information - in a table. In your case, I
would add this column to your AdvertiserTypes table:
ALTER TABLE AdvertiserTypes
ADD IsMerchant char(1) NOT NULL DEFAULT 'Y' WITH VALUES;
ALTER TABLE AdvertiserTypes
ADD CONSTRAINT CK_IsMerchant CHECK (IsMerchant IN ('Y', 'N'));
UPDATE AdvertiserTypes
SET IsMerchant = 'N'
WHERE AdvertiserType IN (2, 6);

After that, chang this line from your query:
AND Advertisers.Adv ertiserTypeId IN @Merchant
to read like this:
AND AdvertiserTypes .IsMerchant = @MustBeMerchant
And of course, replace the current @Merchant and @NonMerchant variables
with a single char(1) parameter @MustBeMerchant that you can set to 'Y'
or 'N' to control which advertiser types must be selected. Wrap it all
in a stored procedure with @MustBeMerchant and @startdate as parameters
and POOF! - no more commenting out, no more human error.

--
Hugo Kornelis, SQL Server MVP