Execute business rule runtime

  • CoolMoon

    Valued Member

    Points: 62

    Hi

    I have set of hundreds of business rules that I need to executed based on certain criteria. Just for example

    If product =A

    -          Code > 5   (code should be greater then 5)

    -          Height > 10

    -          Width >5

    If product = B

    -          Height > 8

    -          Width >7

    I want to return the error message when condition doesn’t match. I want to store this rules/condition in database so I can execute based on input parameter (product) and add new rule to database as needed.

    I can add cursor to loop record but I am not able do condition statement (if, else) runtime. (for selected condition coming from database)

    @exeStatus varchar(20)

    If(condition1)   ---this is not working in SQL

    exeStatus = ‘Success’

    else

    exeStatus =’Height cannot be less than 5’

     

    Any suggestion how can I store condition and execute them on run time ?

     

  • John Mitchell-245523

    SSC Guru

    Points: 148769

    No cursor required.  You can do it something like this:

    CREATE TABLE Rules (
    Product char(1)
    , Code smallint
    , Height smallint
    , Width smallint
    );

    INSERT INTO Rules
    VALUES
    ('A', 5, 10, 5)
    , ('B', 0, 8, 7);

    SELECT
    p.Product
    , CASE
    WHEN r.Code IS NULL THEN 'Success'
    WHEN p.Code > r.Code THEN 'Success'
    ELSE 'Code must be greater than ' + CAST(r.Code AS char(5))
    END AS CodeStatus
    , CASE
    WHEN r.Height IS NULL THEN 'Success'
    WHEN p.Height > r.Height THEN 'Success'
    ELSE 'Height must be greater than ' + CAST(r.Height AS char(5))
    END AS HeightStatus
    , CASE
    WHEN r.Width IS NULL THEN 'Success'
    WHEN p.Width > r.Width THEN 'Success'
    ELSE 'Width must be greater than ' + CAST(r.Width AS char(5))
    END AS WidthStatus
    FROM Products p
    LEFT JOIN Rules r ON p.Product = r.Product;

    John

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

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