Manage Your Business Rules in T-SQL Query

Question

Manage Your Business Rules in T-SQL Query

tomaz.kastrun

SSCrazy

Points: 2141
More actions
September 2, 2022 at 8:21 pm

#4079428

Comments posted to this topic are about the item Manage Your Business Rules in T-SQL Query
Tomaž Kaštrun | twitter: @tomaz_tsql | Github: https://github.com/tomaztk | blog: https://tomaztsql.wordpress.com/
September 3, 2022 at 1:16 pm

This was removed by the editor as SPAM
September 3, 2022 at 1:18 pm

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

I'm not sure I am convinced. How do you manage version control for the database objects which are being generated? Doing this means that you lose many of the consistency checks which SSDT provides during the build process.

Note that double-dot notation in T-SQL is not best practice (eg, master..spt_values) – schema name should be explicit.

In the 'Motivation' section, you have the following text: "At some point in the carrier ..." I do not understand what you mean by 'carrier' here, can you clarify?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

tomaz.kastrun SSCrazy Points: 2141 More actions · Answer 2

Thanks Phil.

I totally relate to your concern regarding the version control; there are pro/cons. But so-far I have not come to a bullet-proof solution.

I have solved this with 1) either pushing the rules also through version control, 2) running rules on "metadata" layer that would be separate from the production; solemly for purpose of documentation, governance and master data management or 3) in form of post-deployment scripts.

Double-dot notations. Agree 🙂 "old habits die hard"

Carrier...autocorrect -> should be carrer. will fix it 🙂

Tomaž Kaštrun | twitter: @tomaz_tsql | Github: https://github.com/tomaztk | blog: https://tomaztsql.wordpress.com/

Thomas Franz Hall of Fame Points: 3897 More actions · Answer 3

I wonder, where it would really help me.

The usual business logic (as total = amount * single_price or gross_price = net_price * (1 + tax_rate)) does not really be needed to parametrizised. Of course there should be a table for the tax rate based on order time and maybe state and maybe product_category etc., but this is a whole other ticket and can / should be done with JOINing the tax_rate-table.

In my previous job we had a similar system for the user interface, which had a ton of shown lists that could be sorted / filtered / limited by the user in almost any combination. So we had the a base script for e.g. the order table:

SELECT *
  FROM dbo.orders AS o
 INNER JOIN dbo.customers AS c
    ON c.customer_id = o.customer_id
 WHERE 1 = 1
   AND c.last_name LIKE @last_name
   AND c.country   LIKE @country
   AND o.order_date  >= @order_date_from
   AND o.order_date  <= @order_date_to

This scripts was stored in a text file (for source control), but could have been in a SQL table too.

When the user opened the order overview, the mid tier application service took this script (was read once at the service start for perfomance reasons and stored in memory). When the user had applied a filter e.g. in the order_from filter, the application service put this filter in the parameter list for the script, OTHERWISE it removed the whole line for this parameter (the filter field's name was equal to the parameter name). So if no filter was set, there was just a WHERE 1 = 1 given to the SQL server for execution, with only a filter on the last_name, all lines which included an @ (or any other char you defined) except the line with @last_name were removed.

God is real, unless declared integer.

tomaz.kastrun SSCrazy Points: 2141 More actions · Answer 4

Hey Thomas,

Thanks for commenting and sharing your solution.

Great thinking. I have had this similar solution for years and understand it.

But eventually decided to recreate it. My issue with parameters as such was, that it was hard to monitor the values for these input parameters. And ended by storing the values in the table. In addition, it was not that straightforward to manipulate the SELECT list. And my biggest concern was the execution plans using parameters on each clause.

So this solution was aimed to bring visibility to business logic, forcing users to store business logic separately.

p.s.: the " (as total = amount * single_price or gross_price = net_price * (1 + tax_rate))" was just an example, showing that one can also parametrise the SELECT list. No additional logic with TAX/VAT calculation.

Tomaž Kaštrun | twitter: @tomaz_tsql | Github: https://github.com/tomaztk | blog: https://tomaztsql.wordpress.com/