Dynamic query based of business rules
-
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:
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 CTEThank you for your help.
-
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.
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. -
Hello
Thank u for your help.
I'll test it tomorrow.
-
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. -
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?
-
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. -
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...
- 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) -
This was removed by the editor as SPAM
-
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
*/SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
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