I'm also trying to to this. I'm creating a shoe shop and it should be possible to filter the result based on shoe sizes (stored as variants). I have hacked a new control to retrieve all available sizes for selected catalog.
The problem now is that I need to send this to the GetAllProducts function. The GetAllProducts is one of the few functions still relying on store procedures. Since I don't want to alter the standard code I am planning on creating a duplicate of this storeprocedure, and alter it to just handle the things I want (I'm not aiming for a public release).
What I now like to do is to add a new store procedure to the current context. Is there any tutorial about this or guidelines? All answers I find is that NopCommerce is moving away from store procedures but I'm guessing that the getallproduct remains for a good performance ?