Post reply

How to utilize Lookup tables without hard-coding business rules in SQL Statements

  • December 9, 2010 at 5:44 am

    #244071

    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.

  • December 9, 2010 at 6:26 am

    #1260525

    still digesting the question, but i took the time to apply a macro on your sample data for anyone who needs it:

    SELECT '1' AS ID,'1' AS Priority,'1' AS Active,'Small Storage' AS Channel,'100' AS Amount UNION ALL

    SELECT '2','2','1','Pickup Truck','50' UNION ALL

    SELECT '3','3','0','Dolly','25' UNION ALL

    SELECT '4','4','1','26" Box Truck','200'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 14, 2010 at 4:41 am

    #1262108

    please give us more details with expected result.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply