January 23, 2020 at 1:46 am
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 ?
January 23, 2020 at 10:44 am
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply