|
|
Posted by Hugo Kornelis on 10/31/06 23:10
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.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.
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|