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!