Need to declare multiple values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andrew Tatum - 30 Oct 2006 21:00 GMT

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!

Reply to this Message

MC - 30 Oct 2006 21:08 GMT

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

> 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
[quoted text clipped - 35 lines]
>
> Thank you so much!

Reply to this Message

Andrew Tatum - 30 Oct 2006 21:33 GMT

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?

Reply to this Message

--CELKO-- - 30 Oct 2006 22:06 GMT

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
AdminAdvertisers are logically different from mere Advertisers.

SELECT <col list>
FROM AdminAdvertisers
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.

Reply to this Message

Andrew Tatum - 30 Oct 2006 22:45 GMT

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.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 @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 AdminAdvertiserList.AdvertiserTypeId 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.

Reply to this Message

Hugo Kornelis - 01 Nov 2006 00:10 GMT

(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.AdvertiserTypeId IN @Merchant

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

With the current code, this equates to
AdvertiserTypeID 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
AdvertiserTypeID IN ('1,3,4,5')
instead of what you really need, which is one of these two
AdvertiserTypeID IN ('1','3','4','5')
AdvertiserTypeID IN (1,3,4,5)
(Which one you need depends on the datatype of the AdvertiserTypeID
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.AdvertiserTypeId 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.

Signature

Hugo Kornelis, SQL Server MVP

Reply to this Message

Tony Rogerson - 31 Oct 2006 09:15 GMT

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.

Signature

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

> 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
[quoted text clipped - 35 lines]
>
> Thank you so much!

Reply to this Message

Tony Rogerson - 31 Oct 2006 09:43 GMT

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?]

Signature

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

> As per MC - varchar is 1 character.
>
[quoted text clipped - 44 lines]
>>
>> Thank you so much!

Reply to this Message