Manage Your Business Rules in T-SQL Query

  • 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?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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: | blog:

  • 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 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.

  • 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: | blog:

  • Comments posted to this topic are about the item Manage Your Business Rules in T-SQL Query

    Tomaž Kaštrun | twitter: @tomaz_tsql | Github: | blog:

  • This was removed by the editor as SPAM

  • 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