Fancy pants stored procedures?

[Grofit]

Hey there,

Im currently working on a small project where im trying to have everything interact with the DB via stored procedures so there is no actual SQL statements in the app. Now for most things thats easy and great, however ive just come accross something that im not sure if im going to be able to do with Stored Procedures. (Im using MySQL at the moment as my server)

Now i have a flexible search section where people can add many different filters... or they may add none... I basically need an SP which can handle multiple or few inputs and return a set of rows. An example would be that a user could do the following filter:

Type: DefaultType
Weight: > 10
Cost: < 50

For that instance the SQL would be something like:

SELECT * FROM stuff WHERE type = {DefaultTypesID} AND Weight > 10 AND Cost < 50;

seems simple enough, however what if the user did the following:

Type: CustomType
Weight: < 30
Cost: > 10

For that instance the SQL would be very similar:

SELECT * FROM stuff WHERE type = {CustomTypesID} AND Weight < 30 AND Cost > 10;


Now the problem i have is that the user can specify if they want to search > or < or >= etc... and then they specify the value... now if i was manually creating the SQL it wouldnt be hard at all as i can just use a stringbuilder (oh im using C# for the app), then just check the values of the ><>=<== box and apply the relevent input. However as i want to do this through a SP im not sure what the best way to go about doing it is... Its also worth noting that there are roughly 5-10 search filters like the above, so there would be LOTS of possible filters that could be applied.

Should i make an SP that has LOADS of input parameters that may be null then somehow skip the null sections of the query? and for the >< problem should i send over an argument as text that specifies the action to use? or is there a better way...

Any advice would be great!

[Alsvha]

It is possible to send "null" into the SPROC and then just don't filter on them, but it isn't an optimal solution - but it is a usable solution. And IMO I like it much better then dynamic generated SQL.

If you for example have a filter called Weight where you had to supply the maximum value a product must weigh you could do something like this:

Suppose you have an input parameter which is called "@TheWeight" which defaults to null:


SELECT myFields
FROM myTable
WHERE (@TheWeight is null OR (myWeightField <= @TheWeight))


What this does is that if @TheWeight is null the "OR" will return all rows.
If @TheWeight have a value, it isn't null, thus the statement advances to the second part of the OR statement and only returns the rows which have a value in myWeightField less then or equal to @TheWeight.

This can then be expanded with all the filters you need by AND them together:

SELECT myFields
FROM myTable
WHERE (@TheWeight is null OR (myWeightField <= @TheWeight))
AND (@TheHeight is null OR (myHeightField <= @TheHeight))
AND (@TheLength is null OR (myLengthField <= @TheLength))


Hope this helps you.

This works in SQL Server - but seeing as the functionality is just logic based and not database specific, I would guesstimate it would also work in MySQL. If it doesn't do tell me :D

[Grofit]

cheers matey, will give it a try when i get home!

[Grofit]

Hey,

Ive just gone home and tried some of the stuff you mentioned but here is what i have got so far:



BEGIN

SELECT items.type_id, item_details.*
FROM items
JOIN item_details ON item_details.item_id = items.id
WHERE

/* First of all check the simple stuff */
IF CHAR_LENGTH(ItemName) > 0 THEN item_details.name LIKE ItemName END IF
IF ItemTypeID > 0 THEN items.type_id = ItemTypeID END IF

/* Check if we need to do any filtering on cost/weight */
CASE CostFilterID
WHEN 1 THEN (item_details.cost > Cost)
WHEN 2 THEN (item_details.cost >= Cost)
WHEN 3 THEN (item_details.cost = Cost)
WHEN 4 THEN (item_details.cost <= Cost)
WHEN 5 THEN (item_details.cost < Cost)
END
CASE WeightFilterID
WHEN 1 THEN (item_details.weight > Weight)
WHEN 2 THEN (item_details.weight >= Weight)
WHEN 3 THEN (item_details.weight = Weight)
WHEN 4 THEN (item_details.weight <= Weight)
WHEN 5 THEN (item_details.weight < Weight)
END

END$$



Ive ommitted the stored proc stuff except the Begin/End, but the Weight,CostFilterID etc are all passed in as args that part all works fine.

Anyway i need to be able to sort based on all the inputs, your previous example would work for the simple ones like the top 2, but for the other ones, the sorting type is based on the ID, so an ID of one means > whereas 5 means < etc. Also as i dont know how many i would be sorting on i dont know how to deal with the SQL AND which im sure is needed...

This sort of stuff may not be possible with stored procs, although im sure with a bit of magic and tears it should all work. I dont want to resort to having raw SQL in the app, but i need to have this level of filtering available, unfortunatly this is the simplest part, the rest are most complex... BUT the logic is pretty much the same, so if you boffins can help me sort this, im sure i can sort the rest!

[Alsvha]

So just to make sure I understand, but depending on what value for example "CostFilterID" has, you'll need to Order you query differently?
Which field(s) are you going to Order By in which direction in which situations?


Generally speaking, then one way of doing different Order By would be to also make cases in the Order By clause.

However you might be able to utilize other tactics, but I need to know a bit more about the logic of the Order By before hand.

[Grofit]

Hey,

Im not doing any ordering, its purely all filtering, so in the above code example if i were to pass over:


ItemName = ""
ItemTypeID = 2
CostFilterID = 3
Cost = 10
WeightFilterID = 0
Weight = 10


The following SQL should be used:


SELECT items.type_id, item_details.*
FROM items
JOIN item_details ON item_details.item_id = items.id
WHERE
items.type_id = 2 AND
item_details.cost = 10;


As the WeightFilterID is 0 it ignores doing any weight related filtering, and as the ItemName is empty it ignores doing a LIKE on the name. The XXXFilterID is not linked to anything really it just dictates what sort of operator to apply, (i called it filter for lack of a better word)... so the filter IDs produce:

0 = ignore
1 = >
2 = >=
3 = =
4 = <=
5 = <

So in the above example the cost filter was 3 so it applied the = operator to the cost field.

Hopefully you should be able to see what im trying to do now from the above example and the previous SP code.

Again, thanks for your help and advice, finding out simple things online is easy, but when you start doing things slightly outside the scope of the norm it gets alot trickier to find decent information, so thumbs up for everything so far!

[Alsvha]

Ahh - let me see if I get this right now.
So your problem mentioned in this statement (previous post):

Anyway i need to be able to sort based on all the inputs, your previous example would work for the simple ones like the top 2, but for the other ones, the sorting type is based on the ID, so an ID of one means > whereas 5 means < etc. Also as i dont know how many i would be sorting on i dont know how to deal with the SQL AND which im sure is needed...

Is that you can't get a "dynamic" logic operator into the solution.
Meaning that you actually need to combine the filter with the operators >, >=, <= and <

Is that correctly understood?

If it is, then I would think you can use some mathematic which I've used for sorting myself.
Remember that A > B logically will be the same as -A < -B so you can utilize such information to actually get the < and > operators in one comparison by multiplying the values with negative one or one. (think I'll have to blog about this one more then I have as well as it is a useful technique)

The main problem arises from the <= and => and their logical difference from just < and >, but it should be solvable as well, perhaps by making a comparison where you multiply with zero or one respectively if you need them to be equal or not. Math-manipulating like this can be very useful - albeit a bit difficult to read (especially 6 months after you've made it :D)

But I'll wait with spending too much time until I'm sure I understand your problem correctly?

[Grofit]

sorry i said Sort in that previous quote, but i meant filter, im not doing any sorting...

Im just assigning an ID to the operator just so i know in my head what operation needs to be done without passing over a string, which leaves it a bit vunerable...

Its just a case of making a SP that will take this data and generate a dynamic SQL statement basically... The previous post of mine shows the scenario and the current logic etc i think...

[Alsvha]

Ahh - now I think I know what you mean :D
And unfortunately - I don't know of a way to do that without making the SQL in a string either in your code layer or the SP, and then EXEC it.

Personally however, I would think that a handful of stored procedures combined with either view/function might be a better route to go then that :)
But it also brings more work with it. So I'd properly also use dynamic SQL for that :o

[Grofit]

I dont mind making lots of smaller SPs to deal with it all, although i wouldnt know the best way to split it all down as each SP has to contain a query of some sort, i guess i could use them as sub queries or something...

For ease of updating and simplicity sake i guess a dynamic SQL statement will suffice in this instance, thanks for your help though!

[TheCPUWizard]

You are making it more difficult than it needs to be. Instead of "dynamic operators" just pass a min and max and use the null check technique. Depending on what you pass or the pair of parameters all conditions are handled.

X Y (between X and Y
X X (exactly X)
null X (below X)
X null (above X)