How do I utilize a lookup table without hardcoding

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How do I utilize a lookup table without hardcoding

Author

Topic

jazlady
Starting Member

7 Posts

Posted - 2010-12-09 : 07:50:47

How do I use a lookup table (on SQL SERVER) without hardcoding the (business rules) values within SQL statements? How can I use a lookup table to search return the correct values back to the SQL Statement?

For Example - My lookup table looks like:

ID Priority Active Channel Amount
1 1 1 Small Storage 100
2 2 1 Pickup Truck 50
3 3 0 Dolly 25
4 4 1 26" Box Truck 200

In this example the ID is just used to maintain record uniqueness
Priority - is the order of precedence as to how the businesss rules (Channel should be applied).
Active - whether this rule is to be used (1 - on) or not used (0 - off)
Channel and Amount are the business rules to be applied.

Can someone help me? I want the business client to maintain this table - as new rules are added turned on/off I want to Store Proc or SQL Statement to perform the business rules base the information in the lookup table.

My first instinct was to use the Priority and Active flags but keep in mind the business client will have access to change content within the lookup table.

I don't want to hard code these business rules in SQL Statments what is the best way to use business rules within lookup tables in SQL Server without using Contrainst.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-09 : 07:54:26

What do you want to do with the channel and amount?
What will the rules be applied to? A single row of data or a number of rows in a table (is so how many).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2010-12-09 : 08:30:17

I normally do something like


SELECT TOP 1 Col1, Col2
FROM BusinessRules
WHERE Channel = @SomeChannel AND Amount > @SomeAmount
ORDER BY Amount ASC, Priority ASC, ID ASC -- ID used for tiebreak

to get the most appropriate rule matching some parameters. This can be done with ROW_NUMBER ... OVER() if it needs to be applied set-based.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources