I have a table to store customer discounts (SQL 2000). The table has up

to 10 possible levels of minimum qty and discount. Then there are 8

possible discount types.

I created a proc to look for the best discount available. It has to go

though each of the 8 types and 10 levels of discounts per type.

The 8 types have different where clauses so I did not see a way around

having 8 separate sets of syntax.

For the 10 discount levels, I used an int variable (@disclvl) to increment

1 - 10.

minqty1 disc1 minqty2 disc2 .... minqty10

disc10

The proc uses dynamic sql like:

where 'minqty' + @disclvl + ' = blahblah'

I believe this task would be much simpler with a normalized database design

(no repeating groups). I have no idea what a discount type is nor your

business rules for determining the best available discount but the example

below should get you started:

CREATE TABLE dbo.discount_types

(

discount_type int NOT NULL

CONSTRAINT PK_discount_types PRIMARY KEY

description varchar(100) NOT NULL

)

CREATE TABLE dbo.discounts

(

discount int NOT NULL

CONSTRAINT PK_discounts PRIMARY KEY,

discount_type int NOT NULL

CONSTRAINT FK_discounts_discount_type FOREIGN KEY

REFERENCES dbo.discount_types(discount_type),

minqty int NOT NULL

);

--set-based query to determine best discount

SELECT MAX(Discount)

FROM dbo.discounts

WHERE minqty < @order_quantity;

--

Hope this helps.

Dan Guzman

SQL Server MVP

http://weblogs.sqlteam.com/dang/
"Artie" <ar*******@yahoo.comwrote in message

news:ND****************@nlpi070.nbdc.sbc.com...

The age-old question: does size really matter? I mean code size - get

your minds out of the gutter!

I have a table to store customer discounts (SQL 2000). The table has up

to 10 possible levels of minimum qty and discount. Then there are 8

possible discount types.

I created a proc to look for the best discount available. It has to go

though each of the 8 types and 10 levels of discounts per type.

The 8 types have different where clauses so I did not see a way around

having 8 separate sets of syntax.

For the 10 discount levels, I used an int variable (@disclvl) to increment

1 - 10.

minqty1 disc1 minqty2 disc2 .... minqty10

disc10

The proc uses dynamic sql like:

where 'minqty' + @disclvl + ' = blahblah'

Question: Is using dynamic sql and variables like this any more or less

efficient than simply repeating this code 10 times per discount type?

If I simply coded the 10 discount levels I could use a table variable

instead of the temp table I am using with dynamic sql.