As part of a work assignment I've long dealt with a giant, dynamic SQL-creating stored procedure that's used for retrieving a bunch of inventory items. This stored procedure accepts a ton of parameters (maybe like 20+) which, in turn, turn on and off the dynamic SQL that's generated.
Eventually we exec all this SQL and generate a result.
Inventory, unsurprisingly, has special rules about who can and can't see certain items and how many items they can order, that kinda thing. As a result, a lot of this logic gets set in the stored procedure.
As you can imagine, with its reams of dynamic SQL the programmers on my team universally loathe needing to change this darn thing.
Given that we already treat the stored procedure as an 'endpoint' (we let the stored procedure handle everything so there's no inconsistency, letting a user see items they shouldn't, for example), I feel like this would be a great candidate for being made into a proper stand-alone service.
In my mind I kind of imagine doing away with the stored procedure and instead using a straight select into some sort of immutable list on the application side. We'd then need to generate a series of filters which would preform the same kinda filtering the various bits of dynamic SQL are doing now and getting a result out the other side.
What gives me pause, however are the following:
I guess I'm just naive about the best way to go about creating a 'filter chain' like this. We're predominately a Java shop so I've certainly dealt with applying a Predicate to a Collection and all, but I've generally done this with just a few filters. Is there a good example of the best way to sort of set up a 'filtering pipeline,' where you start with some large list and progressively winnow it down or is it really just a matter of having a giant list of filters?
When I think about the above, it strikes me that I'd just essentially be recreating SQL in a non-SQL environment and that seems bad. Our platform is SQL Server/TSQL and it seems like creating stored procedures/functions that accept tables as input is, if not frowned upon, not practiced widely.
Doing things entirely in SQL also makes them very difficult to unit test, from my experience.
As mentioned, we're predominately a Java shop but I'm open to suggestions from any language. SQL Server's a bit less malleable, as you can imagine.
Anyway, this problem's been bouncing around my head for a year or more and I'd love to hear your thoughts!