Dynamic query based of business rules

Question

Dynamic query based of business rules

Hall of Fame

Points: 3186

May 23, 2019 at 5:22 pm

I hope you are well. I need your help and advice on a repetitive task that I would like to automate. Every month my manager asks me to run a series of SQL queries based on the same table and columns. I thought I could create a rules table that would allow him to create his own queries So I created this rules table like this: RuleTable

Based on this table I would like to successively execute 3 queries.

The first query is for the rule '+NET 3 or More' and the query is like that:

 SELECT * FROM Table Where    
 ([QuestionCode] =  'HSZ' AND ResponseCode = 3)
  OR ([QuestionCode] =  'HSZ' AND ResponseCode = 4)

The Second Query is for the Rule 'Age Between 25-35' and the query is like that:

SELECT * FROM Table Where    
 ([QuestionCode] =  'RS2' AND ResponseCode >= 25)
  AND ([QuestionCode] =  'RS2' AND ResponseCode < 35)

The 3rd query is for the rule 'CHN' and 'HSZ' and the query is like that:

SELECT * FROM Table Where    
 ([QuestionCode] =  'CHN' AND ResponseCode = 5)
  AND ([QuestionCode] =  'HSZ' AND ResponseCode = 1)

I would like your opinion on this solution and especially have your help to create this dynamic query Here is the script to create the rule table:

    WITH CTE AS 

    (
    SELECT RuleId = 1
     , NetQuestionCode = '+NET 3 or More' 
    , QuestionCategory = 'HSZ'
    , QuestionCode = 'HSZ'
    , ResponseOperator = '='
    , ResponseCode = '3'
    , RuleOrder = '1'
    , CategoryRule = 'OR'

    UNION ALL 
    SELECT RuleId = 1
    , NetQuestionCode = '+NET 3 or More' 
    , QuestionCategory = 'HSZ'
    , QuestionCode = 'HSZ'
    ,ResponseOperator = '='
    , ResponseCode = '4'
    , RuleOrder = '2'
    , CategoryRule = 'OR'

    UNION ALL 
    SELECT RuleId = 2
    , NetQuestionCode = 'Age Between 25-35' 
    , QuestionCategory = 'RS2'
    , QuestionCode = 'RS2'
    ,ResponseOperator = '>='
    , ResponseCode = '25'
    , RuleOrder = '1'
    , CategoryRule = 'and'

    UNION ALL 
    SELECT RuleId = 2
    , NetQuestionCode = 'Age Between 25-35' 
    , QuestionCategory = 'RS2'
    , QuestionCode = 'RS2'
    ,ResponseOperator = '<'
    , ResponseCode = '35'
    , RuleOrder = '2'
    , CategoryRule = 'and'

    UNION ALL
    SELECT RuleId = 3
    , NetQuestionCode = 'CHN AND HSZ' 
    , QuestionCategory = 'CHN'
    , QuestionCode = 'CHN'
    ,ResponseOperator = '='
    , ResponseCode = '5'
    , RuleOrder = '1'
    , CategoryRule = 'and'

    UNION ALL 
    SELECT RuleId = 3
    , NetQuestionCode = 'CHN AND HSZ' 
    , QuestionCategory = 'CHN'
    , QuestionCode = 'HSZ'
    ,ResponseOperator = '='
    , ResponseCode = '1'
    , RuleOrder = '2'
    , CategoryRule = 'and'
    )

    SELECT *
    Into [dbo].[Rules_Parameters]
    FROM CTE

Thank you for your help.

January 8, 2021 at 5:43 pm

This was removed by the editor as SPAM
January 17, 2021 at 9:24 pm

This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

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

Mr. Brian Gale SSC-Insane Points: 24675 More actions · Answer 1

This may not be the most elegant solution, but I believe it should work for you. Just need to un-comment the EXEC(@SQLCMD) line:

DECLARE @SQLCMD VARCHAR(MAX) = '';
DECLARE @ruleID INT
DECLARE @ruleOrder INT
DECLARE @MaxRule INT
DECLARE @trimmedQuery VARCHAR(MAX)

DECLARE curseWords CURSOR LOCAL FAST_FORWARD FOR
    WITH CTE AS (
    SELECT RuleID, RuleOrder, CategoryRule, MAX(RuleOrder) OVER (PARTITION BY RuleID) AS MaxRule, 'SELECT * FROM Table Where ([QuestionCode] = ''' + [QuestionCode] + ''' AND ResponseCode ' + [CTE].[ResponseOperator] + ' ' + [CTE].[ResponseCode] + ') ' + [CTE].[CategoryRule]    AS Query
    FROM [dbo].[RulesParameters]
),
CTE2 AS (
SELECT RuleOrder,MaxRule,RuleID, CASE WHEN MaxRule = 1 THEN LEFT(Query,LEN(Query)-LEN(CategoryRule)) WHEN RuleOrder = MaxRule THEN RIGHT(LEFT(Query,LEN(Query)-LEN(CategoryRule)-1),LEN(Query)-LEN(CategoryRule)-26) ELSE Query END AS TrimmedQuery
FROM CTE
)
SELECT RuleOrder, MaxRule, TrimmedQuery FROM CTE2
ORDER BY ruleid, [CTE2].[RuleOrder]
OPEN [curseWords]
FETCH NEXT FROM [curseWords] INTO @ruleOrder, @MaxRule, @trimmedQuery
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@RuleOrder = @MaxRule)
BEGIN
SELECT @SQLCMD = @sqlCMD + @trimmedQuery
PRINT @SQLCMD
--EXEC(@SQLCMD)
SELECT @SQLCMD = ''
END
ELSE
BEGIN
SELECT @SQLCMD = @SQLCMD + @trimmedQuery
END
FETCH NEXT FROM [curseWords] INTO @ruleOrder, @MaxRule, @trimmedQuery
END

CLOSE [curseWords]
DEALLOCATE [curseWords]

Could probably do this with fewer CTE's, but I like to validate things as I go. I see little things that could be optimized (such as building the query in CTE2 instead of CTE and just using CTE to get the MaxRule OR doing everything inside a single CTE), but I also like readable code and I feel that this is fairly readable. What I would recommend with this for testing it is to run it as is but with your data set and with a few little tweaks. Tweaks that will be required:

1 - replace "Table" with the name of your table

2 - Adjust CTE2 to subtract the length of your table name instead of the hard-coded 26 (21 + length of table name).

3 - After validating that the PRINTed statements are what you expect, comment out the "PRINT @SQLCMD" and uncomment out "--EXEC(@SQLCMD)

There may be a more elegant way to do this, but I was trying to make it work with the data set provided as well as allowing for more than 2 rule orders. The above should allow for any max rule order including 1, as long as @SQLCMD doesn't exceed 8000 characters.

This reply was modified 4 years, 11 months ago by Mr. Brian Gale.
This reply was modified 4 years, 11 months ago by Mr. Brian Gale.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Lidou123 Hall of Fame Points: 3186 More actions · Answer 2

Hello

Thank u for your help.

I'll test it tomorrow.

Mr. Brian Gale SSC-Insane Points: 24675 More actions · Answer 3

I should add that there is a little bit of risk with SQL injection depending on what is in the code.

For example, the Query that is being executed is:

SELECT * FROM Table Where ([QuestionCode] = ' + [QuestionCode] + ' AND ResponseCode ' + [CTE].[ResponseOperator] + ' ' + [CTE].[ResponseCode] + ') ' + [CTE].[CategoryRule]

So, lets say your QuestionCode column contains the text:

[QuestionCode]); DELETE FROM Table; --

That will delete all of the data from Table (presuming they have permissions) and will ignore the rest of the code. You do need to be careful with dynamic SQL...

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Lidou123 Hall of Fame Points: 3186 More actions · Answer 4

Hello

Thanks a lot for your help

I am in the process of testing the query.

I've made some changes, including the addition of "and" for the various clauses of where.

Concerning the risk of injunction in the SQL code, I'm thinking of putting a right which prevents deleting the data from the database.

What do you think?

Mr. Brian Gale SSC-Insane Points: 24675 More actions · Answer 5

Putting in the restriction on delete isn't a bad idea, but depending on the permissions the user is granted, it may not be enough. what if they drop that trigger? Or alter permissions? or drop data?

Or assuming they lack those permissions, they could select data from other tables, they could potentially insert or delete data... SQL injection can be quite dangerous. But if you can restrict who can run that query or who can update the tables that handle that query, you should be pretty safe. Or if you reduce the size of the data that can go into a column, you should be safe too. It is hard (but not impossible) to do any dangerous SQL with a CHAR(3) column. I picked CHAR(3) as that was the largest size needed in your sample data to build the dynamic SQL.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 6

Please don't take this the wrong way, but looks like the SQL equivalent of a Rube Goldberg devise.

The easiest solution would be to simply install SSMS on the managers laptop and all you need to do is create the basic queries in one or more .sql files that he can execute when he needs new data.

If your manager has any kind of brain at all, it shouldn't take more than 5 minutes to explain how the WHERE clause works and how to modify it to based on their specific needs.

If you're doing this simply to see if you can pull it off, go for it... but... I would suggest that you do not subject your manager to it.

Just my two cents...

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

Lidou123 wrote:

Concerning the risk of injunction in the SQL code, I'm thinking of putting a right which prevents deleting the data from the database. What do you think?

Totally inadequate. Either you prevent it all or you don't. It's best to avoid any kind of concatenation of user input. It that cannot be avoided, then the inputs must be thoroughly deloused by doing checks against tables and several checks for symptomatic code.

And remember that the goal of someone using dynamic SQL usually isn't to delete some data... it's normally to take some data as a part of a payload or to inject something allowed by lower privs and then have someone with higher privs unknowingly run the injected code.

You just can't afford to screw around with supposed "good enough" code here. It has to be 100% air tight.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 100942 More actions · Answer 8

I would like your opinion on this solution

First, you really need to go back and do logical modeling, aka normalization, before you create tables (you have a table but it should be at least two tables). For example, note that "NetQuestionCode" and "QuestionCategory" are repeated in multiple rows ... that's a NO NO!

Below's a start at if you want to do a logical design first. You always should, but developers never do, they're just in too big a hurry to get to code. But a bad database design will haunt you forever and you'll never really be able to fix the issues with it. Much better to spend a little time up front designing.

Note that for the logical model, the focus is on functionality / business requirements NOT on technical/performance issues. Technical/performance issues do not exist in a logical design: no indexes, etc.. The names are plain-English style and the data types are just to provide a domain (valid set of values), they won't necessarily be the exact data type and, especially, length which you could assign in the physical phase.

Tinker around with the design at the logical level, walking thru things that come up and solve them NOW, BEFORE you've already started writing code. Once you've truly walked thru those things, then -- AND ONLY THEN -- convert the logical design to physical tables.

Yeah, I fudged a bit by allowing either {a literal} OR {a column} in the components design, but I think it's a practical pre-denormalization.

If you'd like to do a logical design first, let me know and I'll help in any way I can.

/*

Rules 
    rule id
        integer
    rule name
        varchar --currently called "NetQuestionCode" although it's treated as a name
    create date
        date 
    status
        smallint ---1=deleted;0=inactive;1=active
    rule category id --in place of QuestionCategory


Rule Categories
    rule category id
        smallint
    rule category --in place of QuestionCategory
        varchar

Objects 
    object id
        int
    object name
        varchar
               

Rule Components
    rule id; integer --references Rules
    component order; smallint --it's not really rule order, you're ordering the conditions (components) within the rule
    literal; varchar --'('/')'/'AND'/'OR'/...; will be NULL if column instead; EITHER literal or column MUST be present, but NEVER both
    object id; int --references Objects
    column name; varchar --will be NULL if literal instead; EITHER literal or column MUST be present, but NEVER both
    column type; tinyint --optional, you could just use char values for all columns
    column operator; varchar --= / <> / LIKE / ...
    column value; varchar --could change to sql_variant for the physical model

*/

This reply was modified 3 years, 3 months ago by ScottPletcher.
This reply was modified 3 years, 3 months ago by ScottPletcher.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 9

I suggest that the results of the dynamic SQL should be to create a view. Your manager can then SELECT from the view.

Likely the view name would be the same as the rule name, but naturally that wouldn't necessarily be required.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 10

Btw, I too would process the rules tables with a cursor to create / generate the final code. I think a cursor gives you much better control and more flexibility in this type of situation.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.